Exporting Tables from report issue/ Split data

[A_L]

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:

Day	User_id	value (User_id counts)
1	user_1	1
1	user_2	100
1	user_3	30
1	...	...
1	user_n	x

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!

[v-diye-msft · Accepted solution]

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.

[A_L]

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!