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]