Easily download data from Power BI reports with Power Automate

Researcher at Microsoft MVP Labs

Overview

Power Automate is Microsoft's process automation (RPA) tool that automates various inefficient workflows in a friendly, code-free way. It has also been integrated into Power BI, and after creating a Power Automate visual in a Power BI report, end users can run automation flows by simply clicking a button in the report.
Through the collaboration of the two, more powerful and flexible solutions can be provided. I will introduce their usage through some practical application scenarios. Today, I will share a case of using Power Automate to export Power BI report data.
The data in the visual chart of Power BI can be exported, but this function is not intuitive, and the application scenarios are limited. Wouldn’t it be more convenient if a button was added on the report page to allow end users to click to download the data?

First of all, let me explain that the export data introduced here is not to export the data locally, but to OneDrive. You first need to have a OneDrive for Business account.

This article takes the customer segmentation report introduced earlier as an example to demonstrate how to add an "export data" function button to the page, as shown below:

The "Export Data" button in the upper right corner will take a look at the specific implementation steps.

Add a Power Automate visual to a report

Click Power Automate in the visualization panel to add it to the canvas,

Then a window will appear, explaining its usage and steps, first add data, drag and drop the data fields that need to be exported into the field box:

Edit flow

Click the three dots (...) in the upper right corner of the object, select "Edit",

Enter the editing window:

You can click "New" in the upper left corner, or you can use the templates below to create a workflow. Here I choose to use a template, and then start editing the workflow:

▌Click on the template in the lower left corner: Update Excel table from Power BI

Excel Online can be understood as Excel on OneDrive. In this interface, refer to the arrow in the figure above, click the three dots on the right side of Excel Online, log in to your OneDrive for Business account, and then click "Continue".

▌Delete an existing step Delete the step "Update a row":

▌Add step①: Edit Click New step, search and find "Edit":

Then click the "Input" field, and in the pop-up window, select the last item "Power BI Table":

▌Add step ②: Create csv table Search and find the operation of "create csv table":

In the "From" column, select the "Outputs" of the previous step in the pop-up window:

▌Add step ③: Create a file

In the editing interface, fill in the following three items:

• Folder path: Click the folder icon on the right to select an existing folder in OneDrive;
• File name: You can name it arbitrarily. If it is a static name, the last exported data will be automatically overwritten after each export. Each export will be a new file name and will not overwrite the original. In addition, the suffix .csv cannot be omitted;
• File content: Select the output of the previous step Output.
  Then click "Save and Apply", and the entire workflow editing is completed.
  

format button

After the above steps are completed and you return to the report page, you will see that a large blue button has appeared on the page. You can adjust the size of the button, select the button, and adjust the text and fill color of the button in the format panel:

Here I changed the text of the button to "Export Data":

This Power Automate button is used in the same way as the normally added button. In Power BI Desktop, you need to hold down Ctrl and click, and it can be triggered by clicking directly after publishing. After clicking, you can see the downloaded data in the corresponding folder of OneDrive:

There are 4 files here, that is, I clicked four times at different times to generate csv files with different names, and the data exported here is dynamic, based on the data after the end user clicks various filters to interact.

Note: The csv file is exported here. The Chinese in it may be garbled when opened directly. You can download it locally, and then use excel to import csv/text to open it. You can search for the specific operation method.

The above is the technique of using Power Automate to download Power BI data. You can easily add a download button to the Power BI report by clicking the mouse in the whole process without any code. If you do not see the downloaded data in OneDrive after you click the button, Maybe one of the above steps is wrong, please read the steps carefully.
Microsoft Most Valuable Professional (MVP)

The Microsoft Most Valuable Professional is a global award given to third-party technology professionals by Microsoft Corporation. For 29 years, technology community leaders around the world have received this award for sharing their expertise and experience in technology communities both online and offline.
MVPs are a carefully selected team of experts who represent the most skilled and intelligent minds, passionate and helpful experts who are deeply invested in the community. MVP is committed to helping others and maximizing the use of Microsoft technologies by Microsoft technical community users by speaking, forum Q&A, creating websites, writing blogs, sharing videos, open source projects, organizing conferences, etc.

For more details, please visit the official website: https://mvp.microsoft.com/zh-cn

Long press to identify the QR code and follow Microsoft China MSDN

Click to apply to join the Microsoft Most Valuable Expert Program~