How to connect to your Power BI Premium Datasets using Excel (with the XMLA End Points)
I have had seen a few questions in the Power BI Community, as well as from other people asking if it is possible to connect directly from Excel to a Power BI Premium dataset from Excel. Without having to log into the Power BI Service or download any .ODC files.
I am going to show you how to do this below.
Installing the latest Client Libraries to connect to Azure Analysis Services
The first thing that I did was to install the latest Client Libraries, which I think allows me to connect to the XMLA end points in Power BI Premium, no harm in installing them.
I installed all the versions from the link below: Client libraries for connecting to Azure Analysis Services
Getting my Power BI Premium Workspace URL
I need to get my Power BI Premium Workspace URL.
- I log into the Power BI Service.
-
In order to get the Workspace Connection, I went to my Power BI Premium Workspace, clicked on Settings
- I then clicked on Premium at the top.
-
As shown below, I can see my Workspace Collection as shown below. I then clicked on Copy
- I then copy this URL and put it into Notepad
Connecting to my dataset using Excel
The final step is to now connect to Excel.
- I open a new version of Excel.
-
I then go to the Data Ribbon, click on Get Data, From Database, From Analysis Services
-
Where it says Server Name, I put in the URL that I copied from Notepad above
- I then change the Log on credentials to “Use the following User Name and Password.
- NOTE: I do not put in any details but rather leave them as BLANK as shown below.
- I then click Next
-
Because I left the Log on credentials as blank the Azure Windows Authentication Window opens.
- I put in my username and password.
-
I then get the Select Database and Table screen.
- This means I have successfully connected to my Power BI Premium datasets in Excel.
- I then select Model and click Finish
-
It then opens the Import Data Window and I leave the defaults and click Ok
-
Once it loads, I now have my normal pivot tables. With the difference being that it is connected to my Power BI Premium datasets
Conclusion
I have shown how to install, get the URL and connect to Power BI Premium datasets using Excel.
This will be very useful for organizations where they would like their people to connect to the same datasets that are being consumed by the Power BI reports.
I do hope that you found this useful and if you have any questions or comments, please leave them in the section below.
Thanks for reading!
[…] Gilbert Quevauvilliers shows how to connect Excel to a Power BI Premium dataset directly, using its …: […]
Hi,
I do not have power bi’s premium version, My company only subscribed to Power BI Pro service.
I would like use my excel’s GET & TRANSFORM (power query) to connect to the power bi service’s dataset and retrieve the data for further transformation for different analysis,
How could i achieve this? what i read from your article above, you can do it becuase you are using Power BI’s premium and not PRO. Do you aware if it could be achieve with Pro subscription?
Hi there
Unfortunately this cannot be done with Power BI Pro, because there are no options for the XMLA Read/Write.
With that being said you can connect Excel to your Power BI dataset hosted in the Power BI Service. You can click on the Analayze in Excel Button when you are looking at the reports?
This is really useful information, and we have a few Excel reports that will really benefit from this. Question though; if we have an Excel workbook that combines data from a Power BI dataset through XMLA endpoint with On-Prem data sources, I need to have the Analysis Services connection added to the gateway in order to refresh it from the Service. I believe this is necessary because we’re combining on-prem with cloud sources. How do I configure the Analysis Services data source in our enterprise gateway?
I tried clicking the link “Optional – Add to Gateway” and then giving it my O365 email and password, but it returns an error: “An invalid connection string has at least one of the passed arguments which does not meet the parameter specification. Please check the data source connection string.”
Hi there
If you want to add an On-Premise analysis services connection here are the details on how get it added to the Gateway: Power BI – Manage your data source – Analysis Services