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]
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.