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
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!