Curbal

Learn how to grow your business with your data and Power BI

You are here: Home / Knowledge Base / Export Power BI data to Excel / CSV (NO row limits!)
< All Topics
Print

Export Power BI data to Excel / CSV (NO row limits!)

PostedSeptember 22, 2022
UpdatedMarch 7, 2024
ByRuth Pozuelo Martinez

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)+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

Was this helpful?

Reader Interactions

Comments

  1. FYI, the Code2 snippet is missing the “+1” after the ROUNDUP function.

    Having the code snippets was a huge help btw! I only noticed the missing +1 after I kept getting 1 less export than needed and then went back in the video.

    Corrected:

    EVALUATE
    ROW(
    “loopsNeeded”, ROUNDUP(
    CALCULATE(DISTINCTCOUNT(‘Labour market'[Index]))/@{variables(‘IncrRows’)},1) +1
    )

    Reply

  2. Great video and with the added code no problems to implement. However – when i do like this, for every chunk of data I get the column namns meaing when I append all chunks to one big table I get this inserted as many times as i fetch data. It is possible of course to handle in post processing, but would be nice to have as clean data as possible. Did you manage this in any way?

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Table of Contents