Curbal has its own social media server on Mastodon. Check it out here!
Welcome to our Knowledge Base

Tip: Start typing in the input box for immediate search results.

Print

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

Export MORE THAN 100k rows of Power BI data to CSV using Power Automate | No ROW limits!

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]
Was this helpful?

Reader Interactions

Comments

  1. 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
    )

  2. 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 🙂

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

Let us know your thoughts...

Table of Contents