Import data from Power BI Desktop into Excel

This post is also available in: de

Hits: 1549

You have created a Power BI data model and want to import the data from Power BI Desktop into Excel? Read on and learn how it works. Read more about Power BI here.

Exporting Data from Power BI Desktop

With Power BI, a variety of different data sources can be connected and the data can be imported. However, exporting data to other programs is not possible without further ado.
Possibilities are offered:

  • In the Power BI Service:
    Analyzing in Excel, described in the official documentation.
  • From Power BI Desktop:
    Export to csv or txt files using R-script. Read my article here (only in German available).
  • From Power BI Desktop:
    Export to an SQL database using an R script. Read my article here.
  • Import data from a Power BI Desktop file into Excel (this post).

Import data from Power BI Desktop into Excel

Although this feature is not offered out-of-the-box, the data can also be imported from a Power BI desktop file into Excel. And it’s even easier than you thought.
The biggest (and only) hurdle is that we need to know the port of the Power BI desktop file.
Preliminary note: All screenshots are in German, because I don’t have an English Excel version. Sorry for any inconvenience

Import steps

1. The first step varies depending on the type of installation.

1a. Versions installed through the Windows Store:
First open File Explorer and enter the path below:
%username%\Microsoft\Power BI Desktop Store App\AnalysisServicesWorkspaces

If you accidentally end up on the Internet with this path, try the following:
C:\Users\USER\Microsoft\Power BI Desktop Store App\AnalysisServicesWorkspaces
Replace USER with your username.

1b. versions not installed through the Windows Store:
First open File Explorer and enter the path below:
%LocalAppData%\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces

In both variants you can see that the folder is still empty if you have not opened a Power BI Desktop file yet.

File Explorer before opening the Power BI file
File Explorer before opening the Power BI file

2. Next, open a Power BI Desktop file. Once the file is open, you can switch back to File Explorer.

3. Here you see a newly created folder. Open it.

folder after opening the pbix file
folder after opening the pbix file

 

4. Select the folder “Data” and select the file “msmdsrv.port.txt” and open it.

Folder Data with msmdsrv.port.txt
Folder Data with msmdsrv.port.txt

 

5. You will now see the port number of the Power BI Desktop file. Copy it.

Port number
Port number

 

6. Open Excel, go to the “Data” menu item, then “Get Data” (the menu item depends on your Excel version) and select “Analysis Services”.

Power Query - Import Analysis Services

Power Query – Import Analysis Services

7. Now enter “localhost:” and your port number (in our example localhost: 58422)

Connection via localhost & port number
Connection via localhost & port number

 

8. Select “Model” and click on Next and Finish

 

9. Specify how the data is to be imported.
and you will get a fully functional pivot table (all data are fictitious demo data!).

Excel pivot table from Power BI Desktop file
Excel pivot table from Power BI Desktop file

 

10. display individual data records

If you right-click on a cell in the pivot table, you can drill down to display the first 1000 detail records.

Limitations

Unfortunately, there are a few restrictions in this variant. So far, I am aware of four restrictions.

  1. Can’t add any more data to the model
  2. No measures can be created
  3. The Power BI file has to be open for the connection to be established and the data to be updated.
  4. change of port number each time Power BI Desktop file is opened

Do you know any other restrictions? Leave a comment and let us share your experiences.

 

Read more about Power BI here.

Happy Querying!

Dear readers, I write my articles for you. It is my ambition to provide you with qualitatively appealing content.
Please take a minute and give me a review or comment below.
Thank you very much.

Post provides benefit
1
Post is understandable
1
Appropriate post length
1
Average
  Loading, please wait yasr-loader

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: