Replace 2021 March:

Now you can export the info direct from Energy BI Desktop utilizing my device, Energy BI Exporter. Learn extra right here.

Replace 2019 April:

When you’re eager about exporting the info mannequin from both Energy BI Desktop or Energy BI Service to CSV or SQL Server examine this out. The tactic defined right here is just relevant for Energy BI Premium or Embedded capacities with XMLA endpoints connectivity.

In my earlier put up I defined find out how to copy and paste information from Energy BI Desktop into Excel or CSV. I additionally defined how simple you possibly can export Energy BI Desktop information to CSV utilizing DAX Studio. As I promised, on this put up I present you find out how to import Energy BI Desktop information to Excel straight. On this methodology you don’t want to make use of any third-party software program and the efficiency is a lot better than the earlier strategies.

Word: The tactic I clarify on this put up is examined in Excel 2016 solely. However, it ought to work for Excel 2013.

In certainly one of my earlier posts I defined find out how to connect with a Energy BI Desktop from Excel. To import Energy BI Desktop information to Excel we now have to do the identical factor. I clarify the best way to hook up with a Energy BI Desktop mannequin straight from Excel, then I present you find out how to use this methodology to import Energy BI Desktop information.

Discovering Energy BI Desktop native port quantity from Energy BI Desktop temp listing

We are able to discover Energy BI Desktop native port quantity in variety of methods defined right here. So on this put up I don’t undergo all strategies.

At any time when we run Energy BI Desktop, it opens a random port quantity. The port quantity is unbiased of the mannequin so it doesn’t actually matter if  we haven’t related to any information sources or even when we haven’t open any saved Energy BI Desktop (*.PBIX) recordsdata. That port quantity is saved in a textual content file named “msmdsrv.port.txt”. So the one factor we’d like is to do is to browse the temp listing of Energy BI Desktop and open the “msmdsrv.port.txt” textual content file. Yow will discover Energy BI Desktop temp folder right here:

%LocalAppDatapercentMicrosoftPower BI DesktopAnalysisServicesWorkspaces

There needs to be an “AnalysisServicesWorkspaceXXX” folder which XXX is a random quantity. Open that folder then open “Information” and Discover “msmdsrv.port.txt”. Open the file to see Energy BI Desktop native port quantity.

Connecting to Energy BI Desktop Mannequin from Excel

Now that we now have the port quantity it’s simple to hook up with the mannequin.

• Open Excel
• Click on “From Different Sources” from “Information” tab from the ribbon
• Click on “From Evaluation Companies”

• Enter the server identify as “localhost:56770” the place “56770” is my Energy BI Desktop native port quantity

• Click on “Subsequent”

• Click on Subsequent yet another time

• Now we are able to rename connection file to make it extra readable. We have to get again to this file within the subsequent steps

• Click on “Browse” to save lots of the connection file in a desired folder

• Click on “End”

• You may cancel “Import Information” as we don’t want it

We efficiently related to Energy BI Desktop and we additionally created a connection file. Let’s transfer ahead.

Modifying ODC connection file

In earlier steps we created a connection file named “Import Energy BI Desktop Information to Excel.odc”. You must discover this file within the folder you chose earlier. When you haven’t chosen any specific folder, the default folder is:

%UserProfilepercentDocumentsMy Information Sources

• Discover the odc file
• Proper click on and choose

• While you open odc file in Notepad, discover“<odc:CommandType>” and alter the command sort from “Dice” to “Default”
• The subsequent line is command textual content. Right here is the trick. It’s a must to exchange “Mannequin” with a DAX question. So if you wish to import “FactFinance” from “Journey Works” you possibly can write the next DAX question:

EVALUATE ‘FactFinance’

Now we power Excel to run the DAX question on high of our Energy BI Desktop mannequin.

• Save the file and shut it

We’re virtually there.

• Double click on the odc file to run it in Excel
• Click on “Allow” once you get “Microsoft Excel Safety Discover”

Voila!

We imported “FactFinance” information from Energy BI Desktop to Excel.

How one can import different tables’ information to Excel?

It’s simple to import different tables’ information to Excel. Simply comply with the steps under:

• Make a duplicate of the identical odc file
• Edit the DAX question

• Create a brand new sheet in Excel
• Click on “Current Connections” from “Information” tab from the ribbon

• Click on “Browse for Extra”

• Discover the brand new copy of odc file you created earlier and click on “Open”

• Click on OK

All achieved!

Please word that when you have hundreds of thousands of rows of knowledge in your Energy BI Desktop you then’ll be capable to load 1,048,576 rows which is most variety of rows limitation on Excel.

So it appears in lots of circumstances importing Energy BI Desktop information to Excel received’t be an choice simply due to most row quantity limitation in Excel.

Within the subsequent article I clarify find out how to export Energy BI Desktop information to a SQL Server database.

So keep tuned.