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 Directly
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 again 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.
One of the features that is asked a lot on Power BI community is how to export Power BI desktop data to Excel OR CSV.
Note: I’d like to make it clear that in this article we are NOT interested in exporting data from a visualisation in a report to CSV.
The first question lots of you might have is “How on earth someone wants to export data from a Power BI Desktop model to Excel OR CSV?”.
Power BI brings Power Query, Power Pivot, Power View and Power Map together in one piece of software. So why not using Excel at the first place to load data from the source? There might be lots of other questions about the reasons that someone wants to export data from Power BI Desktop model to Excel (or any other destinations). The reason could be one of the following that someone wants to export data from Power BI Desktop model to other destinations like Excel, CSV, SQL Server etc.
For some reason you have just a Power BI Desktop file (PBIX) and you don’t have access to the data sources and you need to provide the data to someone who is not familiar with Power BI
You Power BI Desktop consolidates lots of different sources in a single model and it would be very hard to get the same output as you get in Power BI Desktop model in Excel. So an export feature can be super handy
You might have done lots of complex transformations in Power BI Query Editor and replication the same logic on the source system could be much more complex and time consuming, so again exporting data from a current Power BI Desktop model makes sense
You have a bunch of calculated columns created in DAX and you don’t want to go back and redo all the hard works you have already done in Power BI in another environment like Excel
You might want to use the current Power BI data in Cortana Analytics
You are just curious to see if it is possible
None of the above!
But, the reality is that regardless of the reason, lots of people still want to export data from Power BI Desktop to different destinations. So let’s have a look at different workarounds until this feature is not available in Power BI. I’ll explain different ways to export Power BI Desktop data in a series of articles. In this post you learn how to copy Power BI Desktop data to a destination file like Excel or CSV without any third-party software involved. I also explain how easy you can export Power BI Desktop data to CSV using DAX Studio.
Copy Data from Data View in Power BI Desktop and Paste it to Destination
The easiest workaround is simply copy/paste data from Data view in Power BI Desktop.
One of the coolest features in Power Pivot is the ability to define KPIs based on calculated measures. You can create KPIs in SSAS Tabular as well. Unfortunately, this feature is missing from Power BI. In this post I show you a very simple way to import KPIs and use them in Table, Matrix, Multi-row card and Card visualisations in Power BI.
I use the word “IMPORT” as this feature is NOT available in Power BI Desktop yet so we CANNOT create KPIs directly in Power BI Desktop, but, there is work around for it that I explain it in this post.
Latest version of Power BI Desktop
Microsoft Excel (2007 or later)
Power Pivot add-on if using Excel 2007 to 2013 (Power Pivot is already available in Excel 2016)
create desired KPIs on top of your calculated measure(s)
save the Model (Excel file)
import the Model to Power BI Desktop
Let’s go through the whole process step-by-step to see how it works on real world.
Note: I use Excel 2016 and Adventure Works DW SQL Server sample database. If you’re using prior versions of Excel, you have to download and install Power Pivot for Excel. All steps below are pretty much the same.
Open Excel 2016
From Data tab click “Manage Data Model”
Note: In case you’re using prior versions of Excel you need to click “Manage” from Power Pivot tab. All other steps would be the same.
Get external data from SQL Server
Enter server name and database name then click Next
Select “FactResellerSales”, “DimProduct”, “DimProductCategory” and “DimProductSubCategory” then click Finish
A while ago I wrote a blog post about Power BI Publisher for Excel. Today I want to explain some new features added to the publisher. In this post you learn how to analyse Power BI data in Excel. Using the new Power BI Publisher for Excel, not only can we pin an Excel range or chart to a Power BI dashboard directly from Excel, but also we are now able to easily connect to a Power BI service, select any group workspaces and analyse a desired report or dataset.
Desktop versions of Microsoft Excel 2007 and later
Power BI Publisher for Excel add-in will be enabled by default after you install it, however, if you don’t see the “Power BI” tab in the ribbon in Excel you can enable it from File –> Options –> Add-ins –> COM Add-ins –> tick Microsoft Publisher for Excel.
Connect to and Analyse Power BI Data in Excel
Analyse Power BI Service Reports or Datasets in Excel (From Power BI Service)
Previously we could analyse Power BI data in Excel directly from Power BI service by:
Log in to Power BI Service
Clicking ellipsis button of a desired dataset and clicking “Analyse in Excel”
Clicking ellipsis button of a desired report and clicking “Analyse in Excel”
Doing either way, it downloads an “odc” file that could be opened in Excel.
Now you can analyse the data in Excel using pivot tables and pivot charts.
One of the most powerful features in Power BI and Excel is supporting geospatial visualisations. In Excel we can use Map visualisation in Power View, or use Power Map directly. In Power BI, as you know, there are two built-in visualisations supporting geographic coordinate data, Map and Filled map. They work beautifully if you have enough data supported by Bing Maps. But, there are some issues with Map visualisations in both Power BI and Excel. In this post I address some of the issues I faced myself and I’ll provide the solutions for the issues. As “Filled Map” and “Map” visualisations in Power BI are very similar my focus in this post would be on “Map” visualisation. My intention is not explaining Power View and Power Map that much so my focus in this article would be on Power BI more than the other two.
To experiment everything I explain in this post you need to have:
The new SQL Server sample, WideWorldImportersDW (WWI). You can download it here
The latest version on Power BI Desktop (current version is 2.35.4399.381 64-bit (May 2016))
Select Fact.Sales and Dimension.City then load data
Map Issues In Power BI
Wrong Cities in Power BI
Expand the “Dimension City” table
Select “City” column then change its Data Category to City (Data Category is on “Modeling” tab from the ribbon)
Put a Map visual into the page
Put “City” on Location
Put “Total Excluding Tax” on Size
As you see sales distributed across different countries, but, this is not quiet right.
Put a slicer on the page then put “Country” on the slicer
Click “United States” to filter the Map
Oops! This is not quiet right. What happened is that Bing Map Engine gets confused with the city names so that it shows a city with the same name outside of the US, just like New Plymouth which a city in New Zealand, but, the New Plymouth we have in our data source is the New Plymouth from Idaho in the US.