How to IMPORT data from a Power BI dataset – Premium-only

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

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.

get data from Power BI dataset
selecting the dataset to get data from
live connection created to the Power BI dataset

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.

where to find the XMLA endpoint address in the workspace

Then you can copy the XMLA endpoint URL that you will find under the premium tab.

copy the XMLA endpoint URL from the Power BI service

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

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.

get data as IMPORT from a Power BI dataset

Enter the credentials, choose Microsoft, and login with the same account you used for the Power BI service (your Power BI account).

credentials to connect to the Power BI dataset in the service

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.

choose fields, measures and tables to import from the Power BI dataset

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.

Video

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.

Leave a Reply