Tip: Start typing in the input box for immediate search results.
Export more than 100k rows of Power BI data to CSV using Power Automate
In this video I will show you how to batch the Power BI exports so you don’t hit the Power Automate limit of 100k rows.
Table of contents
00:00 Intro
01:40 Code1: Flow to export Power BI data using Power Automate (will hit the limit)
03:15 Explain the logic first
05:50 Modify the flow to batch the exports
06:00 Initialize the row and increment variables
08:15 Code2: Count number of rows in Power BI
09:40 Code3: Convert the array to number
12:00 Start the loop
13:00 Code4: Rewrite the DAX code to filter the tables
14:30 Increment the variables
18:00 Append all exports to one file
Watch the tutorial on YouTube
The code shown in the video
Code1: Here is the DAX query for running a normal export (without taking into account the limit):
The table is called : Labour market
// DAX Query
DEFINE
VAR __DS0Core =
SUMMARIZECOLUMNS(
'Labour market'[jobid],
'Labour market'[Index],
'Labour market'[job_title],
'Labour market'[contract_type],
'Labour market'[City],
'Labour market'[country_code],
'Labour market'[salary_per],
"Median_salary_hr", 'Labour market'[Median salary/hr] )
EVALUATE
__DS0Core
ORDER BY
'Labour market'[Index]Code2: Here is the DAX code for running the count and the necessary loops:
EVALUATE
ROW(
"loopsNeeded", ROUNDUP(
CALCULATE(DISTINCTCOUNT('Labour market'[Index]))/@{variables('IncrRows')},1)
)
Code3: Convert the array to number so we can use it in the loop
outputs('Run_a_query_against_a_dataset_2')?['body/firstTableRows']?[0]?['[loopsNeeded]']Code4: Rewrite the DAX code to filter the tables by the variables
// DAX Query
DEFINE
VAR __DS0FilterTable =
FILTER(KEEPFILTERS(VALUES('Labour market'[Index])), and('Labour market'[Index] >= minIndex,'Labour market'[Index] <= maxIndex))
VAR __DS0Core =
CALCULATETABLE(
SUMMARIZECOLUMNS(
'Labour market'[jobid],
'Labour market'[Index],
'Labour market'[job_title],
'Labour market'[contract_type],
'Labour market'[City],
'Labour market'[country_code],
'Labour market'[salary_per],
"Median_salary_hr", 'Labour market'[Median salary/hr] ),
__DS0FilterTable
)
EVALUATE
__DS0Core
ORDER BY
'Labour market'[Index]More tips and tricks on exporting data
I’ve seen several comments from readers that they don’t know how to apply this logic if you don’t have an ‘INDEX’ column available. In your response I keep reading “You don’t need the id to export the rows, I used it only for checking”. But with code 4, a filter is applied to this ‘INDEX’ column at the first VAR. How should this VAR be adjusted if you don’t have an ‘INDEX’ column?
It stuck in the loop and failed after few minutes
Could you please help me to correct it? Tried everything but I am still getting error. It is stuck in the loop
DEFINE
VAR _DS0FilterTable =
FILTER(KEEPFILTERS(VALUES(‘____Device Status'[_IMEI])), and(‘____Device Status'[_IMEI] >=@{variables(‘MinRow’)},’____Device Status'[_IMEI] 14 Days])
VAR __DS0Core =
CALCULATETABLE(
SUMMARIZE(
‘____ Status’,
‘____ Status'[_IMEI],
‘____ Status'[Site Code],
‘____ Status'[Last Seen],
‘____ Status'[Status],
‘____ Status'[FT],
‘____ Status'[Install],
‘____ Status'[Name]
),
KEEPFILTERS(__DS0FilterTable2),
KEEPFILTERS(__DS0FilterTable3),
__DS0FilterTable
)
Hi,
Your videos are amazing, I have a small question on this.
In this video, you have a column by the name Index, what if we do not have a column by this name? or any such column?
Your response will be highly appreciated 🙂
You dont need the id to export the rows, I used it only for checking.
Can you please share the zip project of above one ? Also, what if we dont have unique index kind of field to build the flow.
You dont need the id to export the rows, I used it only for checking.