This article shows how to export data from Power BI to Microsoft Excel.
You can import data into Power BI from a variety of sources including databases, flat files, REST APIs and cloud services.
Conversely, you can export visualisations and charts from Power BI into Excel data formats like XLSX, CSV, etc.
In this article, you will see how to export from Power BI to Excel using Power BI Desktop, as well as Power BI Service.
If you would like to learn Power BI through formal training, have a look at our Power BI courses.
Exporting Data To Excel In Power BI Desktop
Power BI allows you to create visualisations using various data inputs. For this tutorial, you will be using the input dataset in CSV format.
The sample dataset that you will be using to plot visualisations in Power BI Desktop and Power BI Service can be found here.
Start Power BI Desktop on your local systems. From the dashboard, select the “Get data” option from the top menu. From the dropdown menu select “Text/CSV”.
Here is a screenshot for reference:
A file upload dialogue box will appear.
Navigate to the CSV file that you just downloaded from Kaggle and upload it to your Power BI Desktop.
Once the file is uploaded you will see a dialogue box that asks you whether you want to transform data or load data.
Click the load data button.
The Power BI Reports view will open where you can see the Visualisations and Fields pane as shown below:
Let’s plot a bar chart which represents the average sales per country and deal size. If you want to learn how to create a bullet chart in Power BI, have a look at this piece.
To do so, you need to add the COUNTRY column in the Axis field, the DEALSIZE column in the Legend field, and the SALES column in the VALUES field.
By default, the sum for sales is displayed. You can click on the VALUES field and select the Average to display average sales values instead.
As a result, you should see the following bar chart in your reports view.
Now let’s see, how you can export this bar chart into MS-Excel-compatible formats.
To do so, click on three dots that appear at the top-right or bottom-right of your chart.
From the dropdown list, select “Export data” as shown.
The data exported will be included in a CSV file which you can save to your local drive.
If you open the downloaded CSV file, you will see that it contains detailed information about average sales per country distributed into different dealsizes.
This method of exporting data is suitable for cases where you do not want to share your reports since you have access to the underlying data used to plot the visualization e.g. actual sales values instead of the sum of values, state information for individual sales, etc.
However, when you publish your report live, e.g. on Power BI Service, the underlying data is not accessible to the viewers by default.
To make sure that the underlying data is available to the report viewers, you need to enable this option.
To do so, on your Power BI Desktop dashboard, go to File -> Options and settings -> Options as shown below:
In the Options window, as shown below, you need to select “Report Settings”.
In the right pane, you will see two options under the “Export data” heading.
Check the radio box for the option that reads:
“Allow end users to export both summarized and underlying data from the service or Report Server”.
Click the OK button.
Now publish your report to Power BI Service and then export it by clicking the three dots on the top right or bottom right of your chart as shown below:
You will see the following download options.
Here you can export the Summarized Data or the Underlying data.
The summarized data will return the same results as you saw when you exported the CSV file in Power BI Desktop.
On the other hand, the Underlying data option will return all the underlying data information used to plot your chart.
Also, you can see that here in Power BI Service you have two Excel formats for downloading data i.e. .xlsx and .csv.
Let’s export the “Underlying data” for our chart in the XLSX format as shown in the following screenshot.
Now, if you open the downloaded file, you will see that instead of displaying average sales per country and dealsize, the file contains detailed information about every sale including the country name, product price, status, postal code etc.
Exporting Data To Excel In Power BI Service
In the previous section, you saw how to export data into Excel-compatible formats in Power BI Desktop. You also saw how to enable the option to export underlying data when you publish your report from Power BI Desktop to Power BI Service.
If you are unsure of the differences between Power BI Desktop and Power BI Service, see this article on Power BI licencing.
In this section, you will see how to export data to Excel formats using reports that are directly created in Power BI Service.
In addition, you will also see how to enable the option for exporting underlying data in excel format inside the Power BI Service.
As a first step, you need to log in to your Power BI Service account.
Here is the link to log in to your Power BI Service account: https://powerbi.microsoft.com/en-us/
Once you have logged in, go to your workspace, and then click the “Add content” button.
Select the “Files” option in the following screen.
Click the “Local File” option in the screen below.
Next, you will need to upload your CSV data file that you used to plot your charts in the previous section.
Once you have uploaded your CSV file, you will see the following two items in your workplace.
Click the one with the type “Dashboard” and open it.
The rest of the process is very similar to Power BI Desktop.
A Reports view will be displayed where you can see different types of visualizations along with the columns from your CSV file.
Let’s again plot a bar chart that displays the average of sales with respect to countries and dealsizes.
Your bar plot will be displayed in the reports view as shown below.
Click the three dots at the bottom right of your bar plot and then select the “Export data” option.
You will be presented with two options to export your CSV file: “Summarized data” and “Underlying data”.
However, by default, the option to download the “Underlying data” is disabled.
I will explain how to enable this option in Power BI Service later in this article, for now click the“Summarized data” option and the .xlsx format as shown below.
If you open your downloaded file, it looks like this:
The contents are very similar to the CSV file you downloaded in the previous section, however in this case the file contains formatted data since it is in .xlsx format.
Let’s now see how to enable the option to export underlying data.
On your dashboard in Power BI Service, go to File -> Settings as shown in the following screenshot.
You will see the following pane appear on the right-hand side of your dashboard.
From the dropdown list under the “Export data” header, select “Summarized data and underlying data” option as shown in the figure below and click the “Save” button.
Now, if you export the data from your bar chart, you will see that the “Underlying data” option is also enabled as shown in the figure below.
Click the “Export” button to download the data in .xlxs format.
If you open your downloaded XLSX file, you will see that it contains the underlying data for sales, countries and dealsizes as shown in the screenshot below:
As you’d expect, Microsoft makes it very easy to move data between Power BI and Excel.
It’s also possible to export from Power BI to PowerPoint!
If you would like to know more about Power BI, we recommend this article on Power BI datamarts too.