Power BI – Export Data Set to CSV file overcome +150000 record limit

Let’s say you have a Data Flow to Consolidate multiple Data Sources

image

And next created a PBI Desktop Data set based on these Data Flows.

image

Transformed it using a PBI Power Query and Published it in the PBI Service

image

And put in place a schedule to refresh the data periodically.

To access the data is either using PBI Desktop or use it in Excel (Pivot table)

Downside is that the export data option in PBI Desktop controls are limited to 150000 records ?

image

Or Analyze in Excel, you will have to download and .ODC file and open it…

But it will not download the complete data set in a tabular layout Sad smile

Alternatively you can use Dax Studio.

But the downside is that you need to have PBI Desktop open and Refresh the Data Set each time.

image

You can’t schedule a job to download the dataset or a subset using a DAX query.

This is no good solution … Sad smile

Solution :

I created my own solution … Smile

Which is an application to download all the data directly from the PBI Data Set without opening the PBI Desktop Client !

It is Not depending on the PBI Desktop

Including DAX Query options to filter the data.

And does NOT get cut off by the 150000 record limits.

Reuses the same ODC connection.

image

When first time run it will ask for authentication if it is not run on a prior installed PBI Client.

image

I have also Command Line version that is scheduled to Export all the data a CSV file, containing +/- 280.000 records

image

Bonus :

Other benefits are that all of the features of the PBI Services remain available like Power Query in Data Flows

image

And as well the Scheduling Engine of PBI Services

The data set contains as well all the custom built Measures when downloading !

No need for Power BI Premium Smile

Use cases are that I needed to extract the data to do an ETL upload in a Data Warehouse.

Or Ad-Hoc analysis on the full data set using other tools

2 Responses to Power BI – Export Data Set to CSV file overcome +150000 record limit

  1. Drew McKenna says:

    Hi there, I have been searching for ways to get around the 30,000 row export limit. I see you mentioned two soltuions, an app, and a command line, but i don’t see either posted… I doubt the app will work for me since I odn’t have admin rights, but the cmd version might. Could you post the cmd line solution or how would I go aobut testing it? Thank you so much

  2. Hi Drew,

    The solution is simple it needs an ODC file to get connected to the PBI Service where the PBIX is hosted. Once the ODC is downloaded by clicking any visual to export data from the portal.

    You can use that to connect and export the data to a CSV. or any

    By default it connects to the PBI Service using all the existing security that has been put in place at that moment.

    This means that if your can click on “Analyze in Excel’ in the portal on a report or data set. You have enough access rights to export data.

    The tool I created was not directly intented to be released for download.

    But if you are interested I can created a custom version for you to test it. Once that is done we can see if a custom cmdline version is helping out.

    I will send you a PM in the next coming days.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: