In my last post – available here – I showed how you can export Power BI data to a CSV file using an embedded Power Automate button on your report. However, when sending data from Power BI to Power Automate, there is a 1,000 row limit. This post shows how you can exceed that limit using Power Automate and Paginated Reports.
Note – this approach will also work with less than 1,000 rows.
Approach #2 ( > 1,000 Rows)
The overall concept for this is that you will be creating a Paginated Report that contains all the data fields (including any measures) that you want in the exported file. Below is an example of a Power BI table visual (left) and a simple Paginated Report with the same data fields.
Here is a summary of the steps I took:
- Create a Paginated Report with all applicable data fields/measures
- Add necessary parameters to the paginated report for any field that can be filtered
- Create “Filter” DAX measures in Power BI report
- Add Power Automate button to Power BI report
- Establish a Power Automate flow that exports the Paginated Report to CSV
Let’s walk through each step.
Step 1: Create Paginated Report
This article is not meant to go deep into creating Paginated Reports. However, for purposes of this approach, one can quickly be created by finding the Semantic Model in Power BI service and selecting the option as shown in this image:
This will take you to the Paginated Report builder in Power BI service. Select all the applicable data fields that you want exported. If you report adds a Total line at the bottom, simply remove this. The order of the columns do not matter.
Step 2: Add Necessary Parameters to Paginated Report
This is a critical step that will be related to Step 3. It involves passing filters that are applied dynamically to the Power BI table to the Paginated Report. This is required because when you apply any filters to your Power BI table, you need to also pass this information to the Paginated Report; otherwise the Paginated Report (and the data that is exported) will not be filtered. But – this can be done by using Parameters and passing the applicable filters to the Power Automate flow. This picture provides an example:
A parameter is needed in your Paginated Report for every field that you have slicers in your Power BI report. In my example, my report has two slicers – one for Carrier Name and one for Year.
Here is information on how to create parameters.
Step 3: Create “Filter” DAX measures in Power BI report
Return to your Power BI report. Create a DAX measure for each slicer you have. The measure is to contain values of any slicers that are used. The CONCATENATEX() function works nicely here.
Step 4: Add Power Automate button to Power BI report
Add your Power Automate button to your report. For the data fields, pass over every measure created from Step 3. Do not add any of the table data fields here as we have the actual data in the Paginated Report.
Step 5: Establish a Power Automate flow that exports the Paginated Report to CSV
Create your flow (from the Power BI embedded button in Step 4). This step involves pulling all the filters passed from the Power BI button to a format that the Paginated Report can use. Let’s walk through this.
To start, I initialize an Array variable. This variable will be used to hold all filters selected in the PBI report. It is used in the Export to File for Paginated Reports step.
This next section needs to be done for each filter you have.
First, use the Select() action to pull out each filter from the Power BI data (Step 4). Remember, in my example above, I’m passing along two filters: “Year” and “Carrier”. So for this section, I want to just extract out the Year. Also – you want to switch the map to text mode as shown in this picture.
The output of this will be an array which can be converted to a string simply by using the join() function.
Next, use the split() function to separate multiple variables into a comma delimited list.
Lastly, use the “Apply to each” action to loop through the output of the previous step. Within this you will want to append to your original filter array (defined previously) using the json format below. The value in the “name” should be the name of the parameter in the paginated report.
Repeat this process for every parameter/filter that you have. You are now ready to export the paginated report.
Use the Export to File for Paginated Reports action and complete the top section. Then for the “ParameterValues” section, reference the filter variable that you created.
The output of this can then be sent using the Send an email action (or other action applicable to your mail).
And that is one approach to automatically exporting a CSV data file even when you have more than 1,000 rows.