cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mrutan
Helper I
Helper I

power bi export to excel

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

1 ACCEPTED 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

View solution in original post

7 REPLIES 7
v-lid-msft
Community Support
Community Support

Hi @mrutan ,

 

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 documentThe maximum number of rows that the applications can export from an import mode report to an .xlsx file is 150,000.

 

11.jpg

 


Best regards,

 

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

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. 

v-lid-msft
Community Support
Community Support

Hi @mrutan ,

 

We can check the storage mode of each data source from the field panel in the right side:

 

6.jpg

 

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,

 

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

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

View solution in original post

v-lid-msft
Community Support
Community Support

Hi @mrutan ,

 

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,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
MariaP
Solution Supplier
Solution Supplier

Hi @mrutan ,

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors