Many Power BI report consumers want to export the data they see in a table or matrix to Excel or CSV for further analysis. Although Power BI’s native export feature is straightforward, it isn’t always obvious or convenient to every user. In this post, I’ll show how to use a Power Automate flow embedded in a Power BI report, making the export process simpler, more automated, and more powerful.
While exporting directly from the visual is simple, it’s not always the best user experience. Many end-users aren’t aware of the export icon, or they find it inconvenient. An alternative is to embed a Power Automate flow in your Power BI report. This approach not only streamlines the exporting process but also enables advanced capabilities, such as automatically emailing the file to a user or saving it to SharePoint.
Key Limitation: The 1,000-Record Cap
By default, the Power Automate visual for Power BI only exports the first 1,000 rows from a table visual. If your visual has more than 1,000 rows, the flow will only receive and export the top 1,000.
In a series of posts, I’ll be showing different approaches depending on your situation. This includes a very simple approach when your data is less than 1,000 rows. I’ll then explore other approaches when the data exceeds this limit.
Approach #1 (Basic Approach < 1k rows)
Step 1: Add in a Power Automate button to your report and select all the data fields (including any measures) that need to be exported. A simple short cut is make a copy of your table and convert it to a Power Automate button visual.
Step 2: In Power Automate, you can convert the output to CSV with the “Create CSV table” action as shown here:
Step 3: From there, take whatever action you need, such as saving this to a SharePoint site and/or emailing the result to the user. Here the “Create file” SharePoint action is used:
Here is where the output can be email.
This is a quick way to add a user-friendly button on your report for users to quickly export the data in a CSV format. A few things to remember:
- Make sure to include all the data fields in the Power Automate visual that need to be exported;
- Remember the 1,000 record limit
- The output of this is in a CSV format
In my next post, I’ll move into one approach to exporting data when you have more than 1,000 records.