Update 2021 March:
You can now export the data direct from Power BI Desktop using my tool, Power BI Exporter. Read more here.
Update 2019 April:
If you’re interested in exporting the data model from either Power BI Desktop or Power BI Service to CSV or SQL Server check this out. The method explained here is only applicable for Power BI Premium or Embedded capacities with XMLA endpoints connectivity.
In my previous post I explained how to copy and paste data from Power BI Desktop into Excel or CSV. I also explained how easy you can export Power BI Desktop data to CSV using DAX Studio. As I promised, in this post I show you how to import Power BI Desktop data to Excel directly. In this method you don’t need to use any third-party software and the performance is much better than the previous methods.
Note: The method I explain in this post is tested in Excel 2016 only. But, it should work for Excel 2013.
Importing Power BI Desktop Directly to Excel
In one of my previous posts I explained how to connect to a Power BI Desktop from Excel. To import Power BI Desktop data to Excel we have to do the same thing. I explain the way to connect to a Power BI Desktop model directly from Excel, then I show you how to use this method to import Power BI Desktop data.
Finding Power BI Desktop local port number from Power BI Desktop temp directory
We can find Power BI Desktop local port number in number of ways explained here. So in this post I don’t go through all methods.
Whenever we run Power BI Desktop, it opens a random port number. The port number is independent of the model so it doesn’t really matter if we haven’t connected to any data sources or even if we haven’t open any saved Power BI Desktop (*.PBIX) files. That port number is stored in a text file named “msmdsrv.port.txt”. So the only thing we need is to do is to browse the temp directory of Power BI Desktop and open the “msmdsrv.port.txt” text file. You can find Power BI Desktop temp folder here:
%LocalAppData%\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces
There should be an “AnalysisServicesWorkspaceXXX” folder which XXX is a random number. Open that folder then open “Data” and Find “msmdsrv.port.txt”. Open the file to see Power BI Desktop local port number.
