Forum Discussion
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:
| 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!
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.
-
2 Replies
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.
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!
-
