Updated: 10 Nov 2020
I first wrote this article on how to get tabular data from Power BI service in Excel in January 2017. The benefits of extracting tabular data remain the same as they were then, however the process of extracting tabular data has changed. This update today reintroduces the concept and shows the current process for completing the task.
What problem does this solve?
Suppose you want to get a data table from your data model, for example a printable list of customers with their annual sales to date. Let's say there are 50,000 customers. How do you do this?
- You can create a table visual in Power BI, but you can't print it from Power BI.
- You cannot export it to Excel because there is a limit of 30,000 rows. Also, doing so will mean that the data will become stale.
- You can use paginated reports, but that's a premium feature; moreover, it is a completely new area for many people.
- Standard analysis in Excel can only view data in Excel through pivot table, not one record table. It "may" work, but it's clunky (in my opinion).
- You can download the Power BI Desktop file to your PC and use DAX Studio, but you need to download the full model just to get the logs you want.
Create an Excel table connected to Power BI
This technique allows you to write a DAX query and run it from an Excel workbook table with a live connection to an online Power BI dataset. The example I'm going to use in this article is to create the following Products by Color table with the total sales of each color. I am using this as a demo to show you how to do it. If I really wanted to build this table, I would frankly only use a pivot table. But it is the concept that I want to show, not the result.
The steps to complete this process are.
- Create the connection to your PowerBI.com data source
- Create a table that links to the source
- Write the DAX query that gives you the table you want
Here is a tour.
Create a link to PowerBI.com
There are several ways to do it. You can use Analyze in Excel to create the link from PowerBI.com.
- Select the Power BI service dataset
- Click on the additional menu
- Select Analyze in Excel
The other way is to start from Excel.
You can connect directly to a Power BI dataset from Excel. Start with a new Excel file, go to the data menu and click Get Data (# 1 below). Then select From Power BI (# 2 below).
Go to Power BI Datasets (# 1 below), and then select the dataset you want to use (# 2 below).
The Power BI data model connects to Excel and an empty shell of a pivot table appears in the worksheet.
Create a table that links to Power BI
This is where the secret sauce is. The easiest way to create an Excel table linked to Power BI is to first create a pivot table. Just create something simple, like this.
Actually, you don't even need the years, you can just add any measure in the values section, and that's it.
Double-click on any of the data points in the pivot table. When you do this, Excel will create an Excel table that will show you all the records that make up the value in the pivot table.
This new table has an underlying connection to the Power BI service. Now you can delete the sheet from the pivot table; it is no longer needed.
Write the query
To do the next step, you need to learn a bit of the DAX query language. I am not going to go into detail on the DAX query language in this post, as I cover it extensively in other blog articles. This is a good place to start. I will just show how to edit the code of the current table so that you can build the table you need.
To edit the query, I simply right clicked anywhere in the table (# 1 below), then selected Table (# 2 below), then Edit Query (# 3 below).
This opens a dialog like the one below. The connection string at the top can be edited if necessary, for example if you want to change the connection to a different report. The command text you can see at the bottom can be removed and replaced with any valid DAX table query.
To demonstrate, you could write a simple DAX query as follows.
After clicking OK, the query runs on the service and the table (Products in this case) is returned to Excel.
Total sales by product color
Next, to get the total sales by product color, I edited the query as shown below.
And this gave me a table of all the colors of the products and the total sales value of those products as shown below. Interestingly, the number format for Total Sales did not flow into Excel as expected. I'm not sure if that's a bug or not.
I could have used SUMMARIZE instead of ALL to get only the colors with sales.
Once you have a table, you can use Excel's printing capabilities to turn it into a paginated report (if you want). For example, you can repeat the headings on every printed page, etc. To update the table, simply right click on the table and select "Update".
If you want to learn more about DAX as a query language, you can check out my article series that starts here An Introduction to DAX as a Query Language