Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
when exporting from power bi to excel i am finding the numbers are not matching between the website and the export.
The export says 150,000 records max, but is this record max the maximum amount on the final sum or the individual records going into the sum?
my dataset has about 500,000 rows, which sum into about 77,000 rows. However, in excel the final numbers dont match. they are off by quite a bit too
Solved! Go to Solution.
i found the issue, and its a weird one.
Basically, i have a time dimension, which has all time periods, except the oldest period because it falls outside of the rolling 12 months.
On the page, i have a time period slicer, which uses the time dimension, and the grid uses the time dimension. When i have no time periods selected, it shows the correct total at the bottom. However, if i manually choose the oldest date, which is not in the time dimension, it filters out the oldest period. that filtered value matches the export. when no periods are selected in the slider, the individual records do not equal the total at the bottom.
So, if i dont have any time periods selected on the time slider (set it to max and min), the total at the bottom shows the full number. However, the records for the oldest period are not in the grid because its outside of the range on the time dimension.
Basically, i think the export is more correct than the grid at this point because the total on the grid is not displaying the sum of the records, its showing the total amount of all records.
I modified my slider to include all periods, including the oldest period outside of the rolling 12, and it matches exactly now.
thank you to everyone for the help
Hi @Anonymous ,
Based on my test, it can export 150k rows in table visual which summarized from 300k rows. Could you please try to verify if there are any filter or slicer applied when export to the excel? Does the dataset using Import mode?
Based on this document, The maximum number of rows that the applications can export from an import mode report to an .xlsx file is 150,000.
Best regards,
I am unsure what you mean by import mode report. can that be explained further.
I am trying to export from a data grid with like 5 columns, that is being sliced using a slicer. when i hit export, it gives me a count that is different from the subtotal at the bottom of the grid.
Hi @Anonymous ,
We can check the storage mode of each data source from the field panel in the right side:
What kind of data you want to export, summarized data or underlying data? Does the exported data greater than the data in table visual or less?
Best regards,
Hi,
Thank you for the help.
The numbers in the export are lower than the numbers displayed in the power bi. I have summed the numbers in SQL, and it matches the numbers in the power bi website, but that does not match the numbers in the export.
I have also verified my data is stored as import
i found the issue, and its a weird one.
Basically, i have a time dimension, which has all time periods, except the oldest period because it falls outside of the rolling 12 months.
On the page, i have a time period slicer, which uses the time dimension, and the grid uses the time dimension. When i have no time periods selected, it shows the correct total at the bottom. However, if i manually choose the oldest date, which is not in the time dimension, it filters out the oldest period. that filtered value matches the export. when no periods are selected in the slider, the individual records do not equal the total at the bottom.
So, if i dont have any time periods selected on the time slider (set it to max and min), the total at the bottom shows the full number. However, the records for the oldest period are not in the grid because its outside of the range on the time dimension.
Basically, i think the export is more correct than the grid at this point because the total on the grid is not displaying the sum of the records, its showing the total amount of all records.
I modified my slider to include all periods, including the oldest period outside of the rolling 12, and it matches exactly now.
thank you to everyone for the help
Hi @Anonymous ,
Glad to hear that you have resolved your problem. Thank you for sharing this your research, we believe it can benefit more users. If you have any other questions about this scenario, please kindly ask here and we will try to resolve it.
Best regards,
Hi @Anonymous ,
There are many settings that will influence your export. I am not clear on if you are exporting from a visual / table etc.
In ancy case take a look at this article for information :
https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-export-data
Also note some of the Limitations and considerations from this article.
These limitations and considerations apply to Power BI Desktop and the Power BI service, including Power BI Pro and Premium.
To export the data from a visual, you need to have Build permission for the underlying dataset.
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. 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.
If the visualization uses data from more than one data table, and no relationship exists for those tables in the data model, Power BI only exports data for the first table.
Custom visuals and R visuals aren't currently supported.
In Power BI, you can rename a field (column) by double-clicking the field and typing a new name. Power BI refers to the new name as an alias. It's possible that a Power BI report can end up with duplicate field names, but Excel doesn't allow duplicates. So when Power BI exports the data to Excel, the field aliases revert to their original field (column) names.....
Hope this helps,
Maria
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |