Replace 2021 March:
Now you can export the information direct from Energy BI Desktop utilizing my instrument, Energy BI Exporter. Learn extra right here.
Replace 2019 April:
In the event you’re eager about exporting the information mannequin from both Energy BI Desktop or Energy BI Service to CSV or SQL Server test this out. The tactic defined right here is simply relevant for Energy BI Premium or Embedded capacities with XMLA endpoints connectivity.
In my earlier put up I defined how one can copy and paste information from Energy BI Desktop into Excel or CSV. I additionally defined how simple you’ll be able to export Energy BI Desktop information to CSV utilizing DAX Studio. As I promised, on this put up I present you how one can 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.
Observe: The tactic I clarify on this put up is examined in Excel 2016 solely. However, it ought to work for Excel 2013.
In one in all my earlier posts I defined how one can hook up 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 way in which to connect with a Energy BI Desktop mannequin straight from Excel, then I present you how one can 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. You 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 “Knowledge” 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 connect with the mannequin.
- Open Excel
- Click on “From Different Sources” from “Knowledge” tab from the ribbon
- Click on “From Evaluation Providers”
- Enter the server title as “localhost:56770” the place “56770” is my Energy BI Desktop native port quantity
- Click on “Subsequent”
- Click on Subsequent yet one more 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 Knowledge” 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 Knowledge to Excel.odc”. It is best to discover this file within the folder you chose earlier. In the event you haven’t chosen any specific folder, the default folder is:
%UserProfilepercentDocumentsMy Knowledge Sources
- Discover the odc file
- Proper click on and choose
- While you open odc file in Notepad, discover“<odc:CommandType>” and alter the command kind from “Dice” to “Default”
- The subsequent line is command textual content. Right here is the trick. It’s a must to change “Mannequin” with a DAX question. So if you wish to import “FactFinance” from “Journey Works” you’ll be able to write the next DAX question:
EVALUATE ‘FactFinance’
Now we drive Excel to run the DAX question on prime 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.
Easy methods to import different tables’ information to Excel?
It’s simple to import different tables’ information to Excel. Simply observe the steps under:
- Make a duplicate of the identical odc file
- Edit the DAX question
- Create a brand new sheet in Excel
- Click on “Present Connections” from “Knowledge” 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 completed!
Please observe that when you’ve got hundreds of thousands of rows of knowledge in your Energy BI Desktop then you definately’ll have the ability to load 1,048,576 rows which is most variety of rows limitation on Excel.
So it appears in lots of instances importing Energy BI Desktop information to Excel gained’t be an choice simply due to most row quantity limitation in Excel.
Within the subsequent article I clarify how one can export Energy BI Desktop information to a SQL Server database.
So keep tuned. 
