How to Export Power BI Report to Excel?

This Power BI tutorial will discuss different approaches to exporting data from Power BI to Excel.

I recently worked on a Power BI report where I needed to export Power BI data to Excel. I was able to accomplish this task with just three clicks.

This tutorial will explore Power BI export to Excel, how to enable export options in Power BI Desktop, how to export to Excel in Power BI Desktop, and how to export to Excel in Power BI service.

Also, we will discuss how to export data with the current layout, summarized data, and underlying data in the Power BI service.

Power BI Export to Excel

Exporting to Excel from Power BI means taking the data and visuals you’ve created in Power BI and saving them into an Excel file. It lets you have a copy of your Power BI data in a familiar format that is easily editable in Excel. You can further analyze or manipulate the data using Excel’s features.

The image below depicts the Power BI data after it has been exported to Excel.

Power BI Export to Excel

How to Enable Export Options in Power BI Desktop

You must enable the export option presented in the Power BI Desktop to use the Power BI Export to Excel. To enable the export option, follow the below steps.

1. Open Power BI Desktop. Then click on the File tab.

power bi enable export data

2. After that, click on  Options and settings -> click Options.

How to Enable Export Options in Power BI Desktop

3. Select the Report settings which is present under the CURRENT FILE.

Once you select the “Report settings” option, you’ll find three choices under the “Export data.” Then, choose Allow end-users to export summarized data from the Power BI service or Power BI Report Server. Then click OK. Check the screenshot below.

Enable Export Options in Power BI Desktop

This way, you can enable export options in Power BI Desktop.

See also  Power BI Distinct Count with filter [With 5 Examples]

How to Export Power BI to Excel?

Now, let’s go through the steps to export Power BI Desktop data to Excel, one by one.

NOTE:

Before exporting Power BI to Excel, it’s essential to create a report in Power BI. If you’re new to Power BI, consider reading a detailed guide on how to create a report in Power BI Desktop?

Here is the report I’m using for this example. I highly recommend creating a report before proceeding further. Check the screenshot below:

export power bi to excel

Follow the below steps to export Power BI Desktop to Excel:

1. Open the Power BI report you created before in Power BI Desktop.

can you export data from power bi to excel

2. Click on the visual you want to export(In my case, a pie chart). Then, you can see the three dots (), which are present at the top right corner of the visual. Select Export data.

export data from power bi to excel

3. Then choose the location where you want to save the Excel file and Select the Excel File type as CSV. Then click on Save.

how to export power bi data to csv

4. Afterward, when you open the Excel file on your desktop, you can see the data, similar to the screenshot below.

export data from power bi

This way, you can export Power BI to Excel.

Example-2 (Power BI Export Table to Excel):

1. In the Power BI Desktop, select the table visual. Then, you can see the three dots (), which are present at the top right corner of the visual. Select Export data.

power bi export table to excel

2. Next, select the location where you want to save the Excel file, choose the Excel file type as CSV, and then click on “Save.” Then, open the Excel file on your desktop. You can see the same as the Power BI table visual.

export power bi table to excel

You can easily export the Power BI table to Excel using these steps.

Export Data from Power BI Dashboard

Now, let’s look at how to export data from the Power BI service to Excel.

1. Navigate to the specific dashboard containing the visual you wish to export. In my case, matrix visual. Check in the screenshot below.

extract data from power bi dashboard

2. Select your visual and click on the three dots () present at the top right corner of the visual. Then select the Export data option.

can you export data from power bi dashboard

3. When you select the Export data option, the window below will appear where you choose which data format to export: Data with the current layout, Summarized data, or Underlying data.

  • Data with current layout = It means exporting the data in the same layout as you see it in the Power BI report or dashboard, but without any icons, colors, or other formatting you’ve added.
  • Summarized data = It means exporting the summarized data used to create your Power BI visual.
  • Underlying data = It means exporting the raw data used to calculate the data in your visual.
how to export a power bi dashboard

Now we see one by one how to export into Excel.

See also  How to Convert Month Number to Month Name in Power BI?

Export Data from Power BI Service to Excel with the Current Layout

Now, we see how to export data from the current layout of the Power BI service to Excel.

1. Select your visual and click on the three dots () present at the top right corner of the visual. Then select the Export data option.

can you export data from power bi dashboard

2. Next, select Data with current layout. Then click the Export button.

export power bi report to excel

3. The .xlsx file is downloaded to the local system. Then, open it in Excel.

export from power bi online to excel

Follow the above steps to export data from the Power BI service to Excel with the current layout.

Export Power BI Service to Excel with Summarized Data

Now, we explore how to export summarized data from the Power BI service to Excel.

1. Select your visual and click on the three dots () present at the top right corner of the visual. Then select the Export data option.

exporting data from power bi

2. Next, select Summarized data and select File format as .xlsx. You can choose the .csv file format if you want an Excel file. Then click the Export button.

exporting power bi to excel

3. The Excel file is downloaded to the local system. Then, open it in Excel.

power bi online export excel

This way, you can export summarized data from the Power BI Dashboard to Excel.

How to Export Underlying Data in Power BI Service to Excel

Here, we see how to easily export underlying data in Power BI service to Excel.

Select the visual -> click the three dots () -> select the Export data. After that, if you face an error like “The report author turned off this option.” Check the screenshot below.

how to export data from power bi online to excel

To solve this error in the Power BI Desktop, go to the Report setting and select Allow end users to export data with current layout, summarized data, and underlying data from the service or Report Server under the Export data. Then click OK.

how to extract underlying data in power bi

After that, republish your report or visual.

how to enable underlying data in power bi

1. Go to the Power BI service and open the report you publish above. Then Select the visual -> click the three dots (…) -> select the Export data.

export underlying data power bi service to excel

2. Next, select Underlying data. Then click the Export button.

Export underlying data Power BI

3. The Excel file is downloaded to the local system. Then, open it in Excel. You can see the raw data in the Excel file.

A Way to Export Underlying Data in Power BI

You can export underlying data in Power BI service to Excel using this method.

See also  Difference Between Calculated Column and Measure in Power BI

Power BI Export to Excel Limitations

  • You need permission when you try to export underlying data.
  • Power BI allows a maximum of 30,000 rows for CSV and 150,000 for Excel to be exported.
  • Conditional formatting rules applied in Power BI may not transfer to Excel.
  • When you enable the Show items with no data option, the Underlying data will not work during Exporting.
  • Calculated fields or measures created in Power BI might not export with the same functionality to Excel.
  • You can not export more than one visual at a time.

Conclusion

I hope you understand how to export data into Excel in both Power BI Desktop and Power BI Service.

This tutorial taught us Power BI export to Excel, how to enable export options in Power BI Desktop, how to export to Excel in Power BI Desktop, and how to export to Excel in Power BI service.

Additionally, we covered how to export data with the current layout, summarized data, and underlying data in the Power BI service.

You may also like:

>