Power BI Export Data – Complete tutorial

In this Power bi tutorial, we will learn about What is export to in power bi desktop. And also we will discuss the below points:

• Who can export data in power bi?
• Different types of exporting data options in Power BI
• How do I enable export data in Power bi
• Power bi desktop export to excel
• Power bi desktop export table to excel
• Power bi desktop export to excel limit

What is export to in Power bi desktop?

• The export to feature in power bi desktop is used to export the data which is used to create the visualization to Excel or CSV.
• The export to features requires the pro and premium license as well as it requires edit permission to the dataset and report.

Who can export data in Power bi?

• If the user has the permissions to the data, then only the user can see and export data that power bi uses to create the visualization. Not all the data can be viewed or exported by all users in power bi desktop
• Often in power bi, data is confidential or limited to specific users. In all those cases the user will not be able to see or export the data.

Read: How to change data source in Power BI

Different types of exporting data in Power bi

There are two different types of exporting data in power bi these are

• Export Summarized data
• Export Underlying data

Export Summarized data

• This type of export in power bi, shows only the data having column and measure, that are used to create visual.
• If the power bi visual has an aggregate, you can export the aggregated data by using the export summarized data feature.
• Summarized data feature is available in power bi service as .xlsx and .csv and in power bi desktop as .csv
• Select the option for summarized data in power bi, if you want to export data that you see in that visual.

Export Underlying data

• If your power bi visualization has an aggregate, select the underlying data that removes the aggregate.
• Select this option, when you want to see the data present in the power bi visual and additional data from the datasets. And this feature is only available in power bi service.

Read: Power bi calculated column vs measure

How do I enable export data in Power bi?

Here we will see how to export data in power bi. The power bi report designer controls the types of data export options that are available for the user/ consumer. The options are:

1. Allow end-users to export summarized data from the power bi service or power bi report server.
2. Allow end-users to export both summarized and underlying data from the power bi service
3. Don’t allow end-users to export any data from the power bi service.

Let’s enable export data in power bi desktop

• Open power bi desktop.
• Click on the Files -> options and setting -> options in power bi desktop.

• Under Current File, click on the Report setting.
• Under Export data, select Allow end-users to export summarized data from the power bi service or power bi report server. Click on Ok.

In this way you can enable export data in power bi.

Read: Power BI Column Chart

Power bi desktop how to export to excel

Here we will see power bi desktop how to export to excel in three ways

• Export data to excel from power bi desktop
• Export data from power bi dashboard
• Export data from power bi report

Export data to excel from Power bi desktop

Here we will see how export data to excel from power bi desktop.

• In the power bi desktop, select the visualization which you want to export.
• When you select the visual in canvas, you will see the more icon(…), click on it.
• Select Export data from the options.

• Then it will show you to save it as a .csv file in your local computer. Now choose the location where you want to save the excel file, and select the excel file type as CSV. Then click on Save.

• Open your excel file on power bi desktop, in the below screen-shot you can see the data.

Read Power bi treemap

Export data from Power bi dashboard

Here we will see how to export data from power bi dashboard in power bi service.

• In power bi service, open the dashboard
• Select the visualization and click on the More icon(…).
• From the option select Export Data.

• In the Export dialog box, under which data would you like to export, click on summarized data, and under file format select .xlsx. And then click on Export.

• Now in your local download folder on your desktop, you can see the downloaded Excel file, click on it. In the below screen-shot you can see the downloaded excel file.

Export data from power bi report

Let’s see how to export from power bi report. And it is similar to Export data to excel from power bi desktop. The difference between them is here you have to export data from the browser instead of power bi desktop.

• Open report in power bi service
• Select the visualization and then click on the More icon(…) -> Export data.

• In the Export dialog box, under which data would you like to export, click on summarized data, and under file format select .xlsx. And then click on Export.

• Now in your local download folder on your desktop, you can see the downloaded Excel file, click on it. In the below screen-shot you can see the downloaded excel file.

Read: Power bi row level security tutorial

Power bi desktop export table to excel

Here we will see how power bi desktop export table to excel and also we will discuss the pros and cons of power bi desktop export table to excel.

• Open your power bi desktop.
• Go to the Data tab in power bi desktop, in the field pane.
• Right-click on the table, and then select copy table.

• In excel blank sheet, paste the table you have copied from the power bi desktop.
• In the below screenshot, you can see the export table to excel from the power bi desktop.

Pros of Power bi desktop export table to excel

• User can copy transformed and clean data in the case have done any data transformation in Power Query editor.
• Users can copy the entire table, including all DAX calculated columns very easily and paste it on Excel and save it as a .csv or .xlsx.
• No third-party tool is needed to export the table from power bi desktop to excel.

Cons Power bi desktop export table to excel

• This is a good way if users want to export a small volume of data. But if the user wants to export a large amount of data then it takes a long time to load.
• If for any reason users want to export data to an SQL server, then this might not be the best way to get the job done.

Read: Power BI Funnel Chart

Power bi desktop export to excel limit

Let discuss about power bi desktop export to excel limitation. And these limitation is also applied for power bi service, power bi pro and also for power bi premium.

• In power bi desktop and power bi service, the maximum number of rows that can export from an import mode report to a .csv file is 30,000.
• In power bi desktop and power bi service, the maximum number of rows that can export from an import mode report to a .xlsx file is 150, 000.
• In power bi to export the data from a visual, you need to have build permission for the underlying datasets.
• In power bi export using underlying data won’t work:
  • if the version is older than 2016.
  • if the table in the model doesn’t have a unique key.
  • If the administrator or report designer has disabled this feature.
• In power bi export using underlying data won’t work if you enable the ” Show items with no data” option for the visualization power bi exporting
• When using a direct query the maximum amount of data that power bi can export is 16 MB uncompressed data. The result may be that you export less than the maximum number of rows of 150,000. And the reason behind that:
  • If there are too many columns and try to reduce the number of columns while exporting.
  • If there is data that is difficult to compress. If the other factors will increase the file size and decrease the number of rows power bi can export.
• In Power BI, if the visualization uses data from more than one data table, and no active relationship exists for those tables in the data model, Power BI only exports data for the first table.
• R visual and power bi custom visuals are not currently supported.
• You can rename a field i.e. column by double-clicking the field and type a new name in power bi and power bi refers to the new name as an alias.
• It is also possible that a report in the power bi can end up with duplicate field names, but excel does not allow duplicates. When power bi exports the data to excel, the column aliases revert to their original field names.
• If there are Unicode characters in the exported .csv file, the text in excel may not display properly. And if you open the file in notepad, then Unicode will display properly.
• And if you want to open the file that has Unicode in Excel, the workaround is to import to the .csv. Import the file into excel:
  • Open Excel
  • Go to the Data tab
  • Select Get external data -> From text.
  • Then go to the local folder where the file is stored and select the .csv file
• When power bi exporting to .csv, the certain character will be escaped with a leading to prevent script execution when open in Excel and this happens when:
  • The field(column) is defined as type “text” in the data model, and
  • If the first character of the text is one of the following: =, @, +, –
• The power bi administrator can disable the export of data.
• If the dynamic formatting string is applied to a measure in power bi, then the exported data would not preserve this formatting in Excel.

You may like the following Power BI tutorials:

• Power BI waterfall chart
• Microsoft Power BI Stacked Column Chart
• Power Bi Schedule Refresh
• Power Bi Pyramid chart
• How to create Power BI report from SharePoint list + Excel
• 5 Simple and Powerful Power BI dashboard examples
• Power BI Donut Chart

In this power bi tutorial, we discuss Power bi export data using Power BI desktop. And also we discussed the below points:

• Who can export data in power bi?
• What are the different types of exporting data?
• How do I enable export data in power bi
• Power bi desktop how to export to excel
• Power bi desktop export table to excel
• Power bi desktop export to excel limit