Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
A_L
Regular Visitor

Exporting Tables from report issue/ Split data

 

Hello everyone,

I've got problems exporting tables (from where i show some data computed in power bi) as .csv files because the tables is huge and it show the message "quantity of data is excessive, probably it will be a resampling".

I already split my entire data table into days and for some days i can export without problems but for others i can't do it, i was trying to split again theese data tables in half, the structure is like that:

 

DayUser_id value (User_id counts)
1user_11
1user_2100
1user_330
1......
1user_nx

 

i have too many rows because of too many users, how can i split into two or more tables?

Users have names that are not in a numerical sequence, i tried with an index column but it doesn't work as i apply it in power query and this table is taken from Reports.

 

Thanks for everyone that can help!

1 ACCEPTED SOLUTION
v-diye-msft
Community Support
Community Support

Hi @A_L 

 

Please check at below link which told about limitations of export data feature:

Export data from visualizations

Limitations and considerations

  • The maximum number of rows that Power BI Desktop and Power BI service can export from an import mode report to a .csv file is 30,000.

  • The maximum number of rows that the applications can export from an import mode report to an .xlsx file is 150,000.

  • Export using Underlying data won't work if:

    • the version is older than 2016.

    • the tables in the model don't have a unique key.

    • an administrator or report designer has disabled this feature.

  • Export using Underlying data won't work if you enable the Show items with no data option for the visualization Power BI is exporting.

  • When using DirectQuery, the maximum amount of data that Power BI can export is 16-MB uncompressed data. An unintended result may be that you export less than the maximum number of rows of 150,000. This is likely if:

    • There are many columns.

    • There's data that is difficult to compress.

    • Other factors are at play that increase file size and decrease the number of rows Power BI can export.

  •  

This workaround will create chunks of 20k lines which can be exported.

 

1) add an index column to your table called "index"

2) add a new column named "EXPORT_GROUP" to your table (in the example below change TblName to your actual table name) :

 

EXPORT_GROUP = switch (true(),

AND(TblName[Index]>=0, TblName[Index]<=20000), "Group1",

AND(TblName[Index]>=20001, TblName[Index]<=40000), "Group2",

"Group3")

 

you can now create a slicer for the EXPORT_GROUP and export your data in sections. Add more Groups if you have a more than 60k of lines.

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

View solution in original post

2 REPLIES 2
v-diye-msft
Community Support
Community Support

Hi @A_L 

 

Please check at below link which told about limitations of export data feature:

Export data from visualizations

Limitations and considerations

  • The maximum number of rows that Power BI Desktop and Power BI service can export from an import mode report to a .csv file is 30,000.

  • The maximum number of rows that the applications can export from an import mode report to an .xlsx file is 150,000.

  • Export using Underlying data won't work if:

    • the version is older than 2016.

    • the tables in the model don't have a unique key.

    • an administrator or report designer has disabled this feature.

  • Export using Underlying data won't work if you enable the Show items with no data option for the visualization Power BI is exporting.

  • When using DirectQuery, the maximum amount of data that Power BI can export is 16-MB uncompressed data. An unintended result may be that you export less than the maximum number of rows of 150,000. This is likely if:

    • There are many columns.

    • There's data that is difficult to compress.

    • Other factors are at play that increase file size and decrease the number of rows Power BI can export.

  •  

This workaround will create chunks of 20k lines which can be exported.

 

1) add an index column to your table called "index"

2) add a new column named "EXPORT_GROUP" to your table (in the example below change TblName to your actual table name) :

 

EXPORT_GROUP = switch (true(),

AND(TblName[Index]>=0, TblName[Index]<=20000), "Group1",

AND(TblName[Index]>=20001, TblName[Index]<=40000), "Group2",

"Group3")

 

you can now create a slicer for the EXPORT_GROUP and export your data in sections. Add more Groups if you have a more than 60k of lines.

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

Thank you, in this way it worked out!

Of course now i have to export manually each split table but at least it works well!

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.