Export full query to Excel/CSV
Hi,
In Power BI Service when I export the summarized data from a visualization to .xlsx (excel) and .csv format, and in excel I apply a pivot table to that data, I find different values.
When I choose the .csv format I loose data precision, decimal places are lost, and when I apply a sum to a sample of data the total is different then the total in the power bi, because the data are rounded and only after this the sum is applied.
When a choose the .xlsx (excel) format I don't lost the decimal places and the total (sum of all values) is the same as power bi.
So it seems that the export to .csv format its not trustly.
Could you help?
Thanks in advanced
5 Comments
v-qiuyu-msftCommunity Support
Hi Anonymous,
I have tested on my side, export summarized data to .csv file does keep decimal places setting. You can test with my report to see if the same issue occurs.
If possible, please share your pbix file with us to test it. If it contains sensitive data, you can upload it to your OneDrive or Dropbox and send the share link to me via private message.
Best Regards,
Qiuyun YuHi,
I forgot to mention an important detail. The values in my dataset has decimal places, but I formated the column to 0 decimal places.
So in power BI I have this table:
When I export to .CSV:
When I export to .xlsx (excel):
The total on the .csv is 25 and in excel is 26 (25.61), like Power BI.
I am using dummy data, but in my real case with a larger dataset the difference is much more significant.
I'm waiting for feedback
Thanks!
- v-qiuyu-msft
Hi Anonymous,
I have reported this issue internally: CRI 91721912. Will update here once I get any information.
Best Regards,
Qiuyun Yu - v-qiuyu-msftStatus changed:NewtoAccepted
- v-qiuyu-msft
Hi Anonymous,
I got information below:
"This is not an actual issue, it is a misunderstanding of how the format is applied.
The format is stored in the model as a property of a column. Whenever that column is shown in a visual (table, chart, matrix, etc) the format is applied. The format is not used when doing computations inside the Analysis Services, where the data is stored.
The actual data stored inside Analysis Services from the attached PBIX is as follows:
Table1[Column1] Table1[Column2]
a 1.74
b 2.86
c 7.42
d 8.36
e 5.23And the column has a property stored on the model schema:
<Property Name="Column2" Type="Double">
<bi:Property FormatString="0" DefaultAggregateFunction="Sum">Whenever you execute a DAX query to obtain the value of Column2, the decimal value, with all decimal places will be used. Therefore SUM(Column2) will be 1.74+2.86+7.42+8.36+5.23 = 25.61
So to these values we apply the format "0", meaning we round up, so you get total 26.
However, if you take the rounded up values and add them, you will get 25. This is a matter of adding values with a rounding step applied vs adding values and then applying a rounding step. Those 2 operations are not one and the same and they will produce different results.
This is not an issue of exporting to CSV or exporting to Excel. This is a misunderstanding that the total you see in the visual is the same as adding all the values in the visual, which is not true. The totals we show are always "data" totals and not visual totals. The totals do not have any formatting applied when they are computed, the formatting is applied only once the value is shown to the user, way after it was computed in the Analysis Services. So doing a total over the values exported from CSV, which have the formatting applied, is not the same as the total that is computed by PowerBI to show in the visual.
Therefore there is no actual issue here."Best Regards,
Qiuyun Yu