Export Power BI data to Excel / CSV (NO row limits!)
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)+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
Subscribe to my channel
The video is great and the idea very useful. One little thing to improve in the process where you combine the individual csvs to single final one is that by doing so you will get the column names included in the final file several times (each loop generates the column names and appends it with the data …)
I have done it on power automate, check this video:
https://www.youtube.com/watch?v=aQV1v6HI7go&ab_channel=Curbal
Hope it helps!
/Ruth
Hi, very nice and practical case. Im just getting still error message in the DO UNTIL step :
Unable to process template language expressions for action ‘Do until’ at line ‘1’ and column ‘12533’: ‘The template language function ‘greaterOrEquals’ expects two parameter of matching types. The function was invoked with values of type ‘Integer’ and ‘String’ that do not match.’.
Any idea whan can go wrong ?
Share your code on power automate forum to get help troubleshooting it!
/Ruth
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?
Hello! Thank you for this it really helped me get data from a dataverse table which has a limit of 5000 per pull. One issue that I am coming across, and it looks like you are too, is that the column headers get put in every loop. So yours instead of having 102158 rows it actually has 102162 rows meaning that it has 4 extra header rows. Any idea how to remove these extra header rows?
I have done it on power automate, check this video:
https://www.youtube.com/watch?v=aQV1v6HI7go&ab_channel=Curbal
Hope it helps!
/Ruth
Hi!
Thank you so much for sharing this very interesting video!
I am struggling with an error :
Unable to process template language expressions for action ‘Do_until’ at line ‘1’ and column ‘25214’: ‘The template language function ‘greater’ expects two parameter of matching types. The function was invoked with values of type ‘Integer’ and ‘String’ that do not match.’.
I cannot figure out how to work my way around this.
Please help!
Hi Ruth,
I guess I have the same question where I want to use your DAX query but without the index column. How do I modify your code such that I can filter the rows between index X to index Y without having the index column in my dataset?
Will review this when I have some time over!
/Ruth
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.
I couldn’t run it successfully, I’m sorry about that. I can’t find a way to validate my loop if it doesn’t have the index. My table visual has many measures.
well, the index is really needed to be able to loop, it is explicitly mentioned in the code.
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.