Different Power BI Export to table options and what they look like
I was recently helping out a customer and they contacted me asking why was the export option not in the format that they expected.
I had a look and now because there are so many options to export data, each one exports the data differently.
My goal for this blog post is to show you what each export type looks like, so when a user is exporting data, they can export in the format they expect.
With all the exporting options below, it does depend on the export settings that are enabled for each report.
In the Power BI Service, you can find the export settings on the report settings as shown below.
Data with current layout
Exporting in this format it will export the data as you see on your screen.
This is what my visual looks like in the Power BI Service
I then export the data and when I open Excel I see the following below.
Summarized data – .xlsx (Excel) with live connection (5000,000 row max)
When selecting this option to export, it creates a live connection to the dataset and will then export the data.
What is doing is running a DAX query to export the data.
Here are more details of the blog post: Announcing Connected Excel Tables from Power BI (Public Preview)
Also, what you will notice is that because this is running a DAX query it is getting the underlying columns which makes the output look very different as shown below.
Because I was using calculation groups and formatting within the calculation group, this is brought through by default.
Summarized data – .xlsx (Excel 150,000-row max)
This export then gets the summarized data.
It into an Excel spreadsheet as shown below.
Summarized data – .csv (30,000-row max)
This export will export the data into a CSV file.
This exports the data as shown below.
The final option is to export the underlying data.
This then gets the raw data exported as shown below.
In this blog post I have shown you all the export options and what they look like when exported.
I do hope that this will be useful for other users to be able to identify which export option is best suited to their needs.
Any questions or comments are most welcome. Thanks for reading