Get data from a Power BI dataset is creating a live connection, which is the recommended way, because you will get the entire model. However, sometimes you want to import the data from the model so that you can add other things to it (like another data source). The ability to have a composite model over Power BI datasets is coming sometime soon. However, the method explained in this blog post will help you just in the meantime.
Power BI dataset live connection
If you are getting data from a Power BI dataset, the live connection method would be used automatically.
Workspace server URL: XMLA endpoint
Another way to connect to the Power BI dataset is to use the XMLA endpoint or the workspace server URL. This is only available if you have premium or embedded capacity.
Find the XMLA endpoint address
To get the XMLA endpoint (or the Power BI dataset’s URL in the workspace) click on the workspace in the Power BI service, and click on settings.
Then you can copy the XMLA endpoint URL that you will find under the premium tab.
Get data from Analysis Services
Instead of using the get data from Power BI service (which creates a live connection automatically), use get data from analysis services
Then paste the XMLA endpoint URL that you have copied from the service as the server address. You can choose the connect to be based on Import.
Enter the credentials, choose Microsoft, and login with the same account you used for the Power BI service (your Power BI account).
Now the navigator window will show you all the datasets under the workspace, you can go under the model and find all tables, fields, and measures and select anything you want from there to import.
Import read before you import the data from a Power BI dataset
Using import for a Power BI dataset (or even analysis services dataset) is not an ideal type of connection. Because when you import, you just get one table, you have to select the fields and measures you want, and all of that comes as one table. Live connection normally gives you a far better experience as you would have all the tables, their relationships, calculations, etc, and you can even create report level measures.
I would just advise the import from the Power BI dataset, only if what you want is not possible through the live connection. and I would recommend limiting the table to not have so many columns and only selected related fields, so it won’t create a cross join scenario.
6 thoughts on “How to IMPORT data from a Power BI dataset – Premium-only”
This is a nice info to know. Can you confirm if there needs to be a gateway datasource connection to import the data?
Power BI dataset is a cloud-based source, you won’t need a gateway to connect to that. gateway is only needed for on-premises data sources
Is there any way to query an online power bi dataset without having Premium?
you can have a live connection to a Power BI dataset online and you don’t need Premium for that
This is the only way I have found to *import* data into Excel’s Power Query engine from Power BI. Every other option connects live to the entire data model which is not what I need for my particular use case. Until Microsoft adds Dataflow support to Excel Power Query, this method will work great. THANK YOU!
I am glad it helps Caleb!