We’ve all met them: the users whose idea of a ‘report’ is a large table of data dumped into Excel. However many beautiful data visualisations they see, however many times you try to convince them of the benefits of using Power BI to build reports, they just want to know where the “Export to Excel” button is so they can carry on analysing data in the same old way. Sometimes there’s a valid reason for doing this, sometimes not, but all too often internal politics means that you have to accommodate them.
If you’re using Power BI the obvious way to do this is to use the “Export Data” button in Power BI. However, as the documentation notes, there are some limits on the amount of data that can be exported:
- The maximum number of rows that can be exported from Power BI Desktop and Power BI service to .csv is 30,000
- The maximum number of rows that can be exported to .xlsx is 150,000
What’s more, clicking a button to export data and then copying it into an Excel report is a time-consuming, error-prone, manual process that no-one likes doing.
There is another way to get data from Power BI into Excel though: using the Analyze in Excel feature. Rather than using Analyze in Excel to create a PivotTable, which may suffer from the subtotals issue described here if you aren’t using the click-to-run version of Excel 2016 and as a result may be very slow when dealing with large amounts of data, and which will be awkward to get data out of because you have to use Excel functions like GetPivotData(), in this post I’ll show you how to get data from Power BI into an Excel table instead.
First of all you need to create a PivotTable in Excel on your desktop that is linked to a dataset published to Power BI. You can do this either by clicking the Analyze in Excel option from inside the Power BI service in the browser (see here for how to do this), or by using the Connect to Data option in the Power BI Publisher for Excel add-in.
Next, drag any measure into your PivotTable (you will need to have at least one measure defined in your dataset to do this) like so:
Then double-click inside a cell containing values, such as the selected cell in the screenshot above, or right-click on the cell and select Show Details:
When you do this a new worksheet will appear with a table in it containing rows of data from a table in the underlying dataset, but the exact data is irrelevant here. The real point is that you now have an Excel table (not a PivotTable) with a connection back to your Power BI dataset and you can edit the query that it uses to return data from Power BI – a variation on an old trick known to SSAS and Power Pivot users. You can now delete the PivotTable you created because you will no longer need it.
On the new worksheet with the table on it, right-click in a cell and select Table then Edit Query:
A dialog will pop up, and you’ll see an MDX Drillthrough statement in the Command Text box as shown below:
You can replace this statement with any MDX or DAX query you want – I recommend using DAX queries here because they are likely to be easier to write if you already know how to write DAX calculations, and unlike MDX queries they return a tabular resultset. I have a fairly old series of posts on DAX queries here that will help get you going. It will be a good idea to install DAX Studio and write your queries there, then copy them into Excel when you are happy with them.
Once you have a working DAX query in the Command Text box:
You can click OK, the query will run and you’ll see the output of the query in the Excel table:
One minor annoyance is that the column names will be in DAX ‘Table Name’[Column Name] format, and although you can use the SELECTCOLUMNS() DAX function to alias your column names and thereby remove the table names, the square brackets around the column names will always be there.
There are several good things about this approach to getting data into Excel:
- It allows you to exceed the 150,000 row limit of the native Power BI Export option mentioned above
- The queries are very quick to run if you aren’t doing anything complex in them
- It avoids manual exporting – you just use Excel’s native Refresh functionality to run the query whenever you want to download new data into Excel
There are some obvious downsides though:
- There’s no easy way to pass parameters to the queries you use, and so allow the user to choose what data is retrieved from Power BI. I guess it would be possible with some VBA, though – I haven’t tried – and it may also be possible to connect Power Query/Get&Transform to the Power BI Service and then use some of the tricks I show in this video for building reporting solutions in Excel. You can of course use Excel’s own native table filtering functionality to filter the data that is downloaded.
- If your DAX query is slow to run your users may get frustrated. To counter this you could create calculated tables in your Power BI dataset using the same DAX as your query, and because calculated tables are created when the Power BI dataset is refreshed rather than when your user refreshes their Excel worksheet this should speed things up. However it will increase the size of your Power BI dataset and make your dataset refresh take longer. In Excel your DAX query would simply be something like:
EVALUATE ‘My Calculated Table Name’ - Writing DAX can be complex, whether it’s a DAX query or a calculated table, so another option would be to use the Power BI Query Editor to create the table you need for your report, load that into your dataset and then load the table into Excel.
- You can’t publish the Excel workbook up to Power BI and have it refresh automatically, because of course Power BI does not support connections from published Excel workbooks back to Power BI datasets. Hopefully this will change in the future – it marked as planned on the Ideas forum.
Of course doing this goes against the best practices that I and most other people recommend for Power BI, so you should probably only do this if you have no other choice. Sometimes you have to bend the rules a little to make sure your project succeeds…
Love the opening paragraph. I know too many of these users!
Chris, you bring up a good point, and is one I’ve been contemplating myself for some time. MSFT has put themselves in an interesting position. They’ve created a great data mashup experience (PowerQuery — my users love this) and have a top-notch engine to perform analytics on the end result, but there are times when you want access to that data outside of PowerPivot/Power BI/SSAS. Of course, it doesn’t look like MSFT is going to make that easy for people because they want them contained within their ecosystem. Against my better judgement, I’ve used SQL Import and Export Data tool to export data from a tabular model hosted within PowerPivot/Power BI before, and there are users that want to leverage Python or other tools to integrate with that engine. In one way, I think it’s a terrible idea to use the Tabular engine for building any additional integrations off of, but on the flip side it’s also not great to have to rebuild calculations/APIs for data access that could yield different results from your Tabular model. What do you think?
Every word in the rock.
I used this approach in my previous job, and it was one of the most successful “report” ever, some users want to act on the most detailed data, others take those Excel dump and create their own reports using other tools, obviously a lot of users are just happy with the PowerBi reports as it is.
PowerBi is a great tool, but there are a lot of scenario were Excel is much better, nothing can beat a Pivot Table yet, and there is no way to filter a table visual or simply print a table with multiple pages in PBI.
I hope Microsoft open up PowerBi to other third party tools too,right now even Powerquery can not access a data model hosted in a PBI
Good work, but not Direct Query connections…
It does work as long as you enter a valid DAX statement.
Great tips. I will try this
Excellent article, thanks very much for sharing. I too find the need to create “Data Dump” reports from my tabular model. I use Power Query to connect to my SSAS tabular model. From there it’s just point and click.
Hi Chris. Great post.
Did you dive deeper in to connecting directly to Power BI service from Power Query? I have made an attempt using the SASS connection in Power Query but I get “we couldn’t authenticate with the credentials provided”. I am not sure if this is a Power BI restriction or due to access to Power BI only possible via the company VPN.
I did try it and it does not seem to be possible – at the time it seemed to me to be an authentication problem too.
Dataflows can now do this.
I have seen the entire video. Congratulations. Power query can use as an input many excel worbooks or tabs but I have not seen the reverse. i.e. any way to create as an output many workbooks or tabs from just one query. There’s no M function “save query output as an Excel file”.
The final transformations of my query are many filters in a column, and the output of each filter must be in a file in order to be emailed. When “close and load” you only generate one file…
Do you suggest any idea to accomplish this task? Many thanks in advance.
Maybe I could carry out the filters further with VBA… I wouldn’t like a solution as to use R in Power Query, but I’m afraid it is the only solution. I would love to do all in Power Query!
You can’t have more than one output from a query, unfortunately – the best thing you could do would be to create a function and then create multiple queries that call it with different values.
Why not use a pivot table?
Thank you Andrew! I have evaluated the option “Show Report Filter Pages” from Pivot Tables… but the filter is not related with another table. But now with “Power Query online” (DataFlows) and Microsoft Flow I believe I can automaticly split my Excel file in multiple files… I am trying & enjoying with these new feeatures!
Is it possible to implement this with Direct Query?
Is it possible to do this in Direct Query mode?
Yes, it should work in the same way.
I’m prompted with a “DirectQuery Error: MDX/SQL operations are not supported in DirectQuery mode.” upon double-clicking or right-clicking to “Show Details”. I’m assuming there’s no workaround?
Ah, are you using DirectQuery with an older version of SSAS Tabular? That would not work.
Ahh okay, that’s what I get for bypassing the update. Thank you for your help!
If wonder if using dataflows to create a large Excel file (table) through M code then I could recover the transformed table in some way… Maybe with a Premium Licensce? DataLakes? If had a solution I could ask for a better license… It’s a pity to have the chance to transform large tables but it is imposible to get the data without tips as the one showed here…
1. “You can do this either by clicking the Analyze in Excel option from inside the Power BI service in the browser (see here for how to do this), or
2. by using the Connect to Data option in the Power BI Publisher for Excel add-in.
Both ways require a Power BI Pro license $9/month, which as a college student who only uses sometimes for a class, is not an option for me 🙁
Thanks for sharing. I’m learning from your blog and all the pros who posted here.
Is it possible to use this feature in Power BI Report Server? If so, how can we do it?
No, I don’t think it is possible because “Analyze in Excel” is not supported in Power BI Report Server
At some point in the past year, perhaps with an update to Excel, these connected DAX tables stopped refreshing upon the “Refresh All” command. Neither will they refresh when you try to refresh from the Queries & Connections pane. The only way we can get these tables to refresh now is right-clicking on each table and clicking refresh, so our solution for each connected workbook containing DAX tables has been to write macros which go through selecting and refreshing each table, then executing a Refresh All. Has anyone else run into this? Is there a solution?
Hi Chris, this is great but I need to export the total row.. How I get it?
Do you mean you want to write a DAX query that returns total values? That’s certainly possible.
No, I really want to export the totals, that already exist in the original table, to Excel.
I totally understand the export-to-Excel question.
I’m a total beginner with BI but I’m good with Excel. Those tools that are available in PBI are great, easy to use even for me.
I use simple things, learning by doing. Ie. I load a table, delete first three rows, promote the first row to headers, convert some data, and create two joined table.
The result is still a table and I obviously I want to export it to Excel, because I’m good in Excel and I want to continue working on this table in a tool that I’m familiar withj. In time I will probably get more knowledge about PBI or Excel and it might turn out that I can do the entire workflow in either of them but currently I do the data preparation in PBI and do the calc in Excel.
Why would you prepare your data in Power BI if you want to do your calc in Excel though, when you can do exactly the same thing inside Excel with Power Query/Get&Transform there?
That is missing knowledge 🙂 I was aiming for more complex data management and visuals, but ended up with a so simple task that can be done in Excel as well. I’ll probably leave PBI for the moment and do this in Excel, thank you for pointing this out.
Thanks for share with us. But i try zetexcel.com and i found this useful and easy
Hi Chris, any idea why these connected DAX tables do not refresh with a workbook “Refresh All”? Neither will they refresh when trying to refresh from the Queries & Connections pane. The only way we can get these tables to refresh is right-clicking on each table and clicking refresh.
I am having the same issue, and cannot find the root cause of it.