Power BI Export to Excel

By: Kenneth A. Omorodion   |   Updated: 2024-09-12   |   Comments   |   Related: > Power BI

Problem

Recently, I was asked by a colleague to help export large volumes (tens to hundreds of millions of rows) out of Power BI to either CSV or Excel files. Unfortunately, it was not a straightforward task due to a limitation on the maximum number of rows of data that can be exported to Excel or CSV.

Although this is not a new concept, we can leverage DAX Studio to export the data from the Power BI data model. In my experience, I have found that, with an import model, the data tables can easily be exported, but that's not the case with tables with a DirectQuery source. This tip will describe some approaches to managing this.

Solution

For those who only need to extract each table in your Power BI model to Excel or CSV, I will outline how to achieve this with DAX Studio (if your model is on import mode, not DirectQuery).

If your model is in DirectQuery, I will also demonstrate the issues you might face and an approach I have used to bypass this limitation.

What are the Reasons for Exporting Data from Power BI?

• If end users don't have direct access to the backend data source feeding the Power BI model.
• If end users want the already cleaned, structured, and modeled data in a Power BI model.
• If the Power BI data table is derived from several sources and combined (merged or appended) in the Power BI model.

Why Can't I Use the 'Export data' Feature in Power BI?

This option is fine and will work as required when you have a few thousand rows of data. To be precise, when data goes above the expected limits in Excel or CSV, this would not work. It will only export the amount of data that Excel can hold and filter off the others above the threshold.

I will illustrate this using a sample Power BI model in the image below.

In the above data model, we have five tables in import mode (Manufacturer, Product, Sales, Geography, and DimDate) and one in DirectQuery mode (SalesOrderDetail).

The 'Sales' table is the largest, with over 7 million rows of data, as seen in the image below.

I can create a table visual in Power BI using the 'Sales' table (see the image below) and then export the data to Excel using the Export data feature (see the image below).

The above action would generate the following message:

If you decide to continue exporting it to CSV after the above warning message, you will observe that the number of rows returned in the CSV is far less than what is in the actual data from Power BI. This is where we now need to use other approaches to export the data to CSV from Power BI.

How Can I Export Power BI Import Mode Table Data to CSV?

To achieve this, you will need to download DAX Studio and install it on your laptop or PC. You can download the latest release of DAX Studio via this link. When this tip was written, the latest release was version 3.0.11.

Once you install DAX Studio, connect it to your Power BI data model by following the instructions/steps below.

1. Ensure you have your Power BI desktop report opened.
2. Ensure that DAX Studio is open, too (if you have it as part of your Power BI External Tools, then click to open it).
3. Connect to your Power BI data model via DAX Studio, as seen in the image below. See these instructions on how to connect DAX Studio to other platforms.

Once connected, you should be able to view your tables on the left pane in DAX Studio, as seen in the image below.

Also, monitor the log recorded at the bottom of the DAX Studio screen.

So, to export all tables (remember, we have both import and DirectQuery tables) in the model, you need to click on the Advanced tab at the top of the screen. Then, under the Export group, click on Export Data, as seen in the image below.

The new window that pops up gives you the option to export the data to CSV Files or SQL Tables. I will describe the option for SQL tables in a future tip. For this tip, I will select the CSV Files option.

In the new window, I unselected the defaults and only selected Comma, as seen in the image below. But you can select other delimiter options as you wish.

Next, you will need to define a path for the output exported data by clicking on the ellipsis, as seen in the image above. Then, either create a new folder on the fly or choose a folder location to save the exported data. Click Next, then Export.

As you can see in the image below, all import mode tables (including the over 7 million rows of the 'Sales' table) were exported to CSV.

But the 'SalesOrderDetail' table, which is on DirectQuery mode, failed to export to CSV. We will investigate why it failed in the next section. For now, let's see the tables in the location/path we set (image below).

If you open each CSV file, you will see the full exported data is there. If the files are larger than the limits in Excel, you might want to use DAX to filter the data before exporting it (I will explain how to achieve this later).

You would also find that the SaleOrderDetail DirectQuery table was exported. However, when you open the file, you only find a thousand rows, while the table had 121,317 rows in total. Let's see why it is failing and how to work around it.

How Can I Export Power BI Direct Query Mode Table Data to CSV?

The reason the SalesOrderDetail table did not fully export to CSV files like the import tables is clearly seen in the image below.

As mentioned earlier, DAX Studio cannot export a table fully when its source is DirectQuery.

So, to bypass this issue, I create another copy of the .pbix file, then open the Power BI desktop file and change the storage mode of the DirectQuery table to either Dual or Import mode. The image below shows the initial storage mode of the SalesOrderDetail table.

The model below shows the changed storage mode of the same table.

So, if all initial steps are followed, the full SalesOrderDetail table will now exported to CSV (see image below).

The outputted CSV file in the image below now shows the SaleOrderDetail export has a size of 12,470 KB, compared to the 1,100 KB for the DirectQuery export earlier. Also, if you open the file, you will find the total number of rows.

Considerations of the DirectQuery to Dual or Import approach:

1. The higher the volume of data, the longer it may take to refresh.
2. The higher volume of data may result in a longer time for the table to be exported.

What If I Need to First Filter the Table(s) Before Exporting to CSV?

One advantage of doing this is to reduce the volume of exported data if you do not need all rows of the table. This is even more important because it becomes difficult to work with data that has more than the limits of rows in Excel if the exported data table has millions of rows.

So, how do we achieve this? We will still be using DAX Studio.

For this example, I will use the 'Sales' table from earlier. Instead of exporting the whole 7 million+ rows, I want to only export where the data row is on or after June 2021. I will first use DAX to define this in DAX Studio. See the image below.

As you can see, it has been reduced to about 640K rows, which is manageable in Excel.

Your DAX query might be different, but the concept is the same.

To export the data to a CSV file, you need to first click on Results on the Output group on the DAX Studio ribbon as seen in the image below. Then select File.

After this, run the DAX query again, and then a new window should open where you can provide a name for your exported file and the location where you want to save it. That's it!

Summary

Exporting large datasets from Power BI to CSV files can be challenging, especially when dealing with limitations imposed by Power BI's native export features. DAX Studio emerges as a powerful tool to overcome these challenges, particularly for models in Import mode, where direct export is seamless. However, when working with DirectQuery mode tables, the process is not as straightforward due to inherent restrictions that limit the number of rows that can be exported.

By changing the storage mode of DirectQuery tables to either Dual or Import in a duplicated Power BI file, users can successfully bypass these limitations and extract the full dataset. Additionally, DAX Studio offers the flexibility to filter data before export, allowing for more manageable file sizes and targeted data extraction, which is crucial when dealing with large volumes of information.

This approach not only enhances the efficiency of data handling but also ensures that end users can access the complete cleaned and structured data they need. As more users seek to leverage Power BI's capabilities, understanding how to effectively use tools like DAX Studio becomes essential for maximizing the potential of their data analytics workflows.

Next Steps

• Read more about DAX Studio via their documentation page.
• Read more on writing DAX queries in DAX Studio.
• Check out the other features in DAX Studio to know more.
• For more information about the installation of DAX Studio.

Learn more about Power BI in this 3 hour training course.

About the author

Kenneth A. Omorodion is a Business Intelligence Developer with over eight years of experience. He holds both a bachelor’s and master’s degree (Middlesex University in London). Kenneth has the MCSA, Microsoft Data Analyst - Power BI and Azure Fundamentals certifications. Kenneth is a Microsoft Certified Trainer and has delivered corporate training on Power BI, SQL Server, Excel and SSRS.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips