Forum Discussion
Issues with .csv Export from Power BI to Excel – Incorrect Formatting in Excel
Hello Power BI Community,
I am encountering an issue with exporting data from Power BI to a .csv file and then opening it in Excel. Here’s a brief description of the problem:
Context:
I have a table in Power BI that contains financial data, and everything appears correctly within Power BI, including the proper formatting to two decimal places.
After exporting the table to a .csv file, I noticed strange values for one of my columns when opening the .csv in Excel.
Problem:
One specific column ("Fees") shows scientific notation (e.g., 1.501392915E+08) instead of the actual values when opened in Excel.
The .csv file looks fine when opened in a text editor. Here’s a snippet for reference:
Project name,Fees,Revenue
Alpha,150139291.5,"3921,23"
In Excel, the "Fees" column is not displayed with the correct formatting, leading to potential inaccuracies in data interpretation.
Additional Details:
In Power BI, the "Fees" column is formatted to two decimal places.
I want to maintain the exact accuracy and formatting from Power BI when viewing the data in Excel.
Question:
What is the best way to open the .csv file in Excel to ensure that the data, especially the "Fees" column, is displayed with the exact accuracy and formatting as it appears in Power BI?
Thank you in advance for your help!
--
UPDATE - I just did the following:
1) Open .csv file in .xlsx file
2) Change data types
Project (text)
Fees (text)
Revenue (general)
3) Replace "." with "," in fees column
4) convert back to number datatype
This is a very long process though. Any tips?
CSV generally has no meta data - no formatting, whatsoever. If you want the data in an Excel friendly format (for your locale) then you need to preformat it accordingly in Power BI.
Hi JohnZiad555 ,
Based on my testing, please try the following method:
1.Go to the Data tab in the Excel.
2.Click get data > From Text/CSV.
3.Select the file and choose import.
4.Select Load or Transform.
But, the Excel cell has digit limitation. The max is 11. If the data beyond 11 digit, the number do not display.
Best Regards,
Wisdom Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
CSV generally has no meta data - no formatting, whatsoever. If you want the data in an Excel friendly format (for your locale) then you need to preformat it accordingly in Power BI.
@Ibendlin is correct. May I add, that in addition, if the concern is display
as Scientific Notation, than the answer would be to change the
Formating of the Cell. This has always been true (as long as my memory serves),
and a small task when preparing an Excel report for distribution.
Explanation:
By default, Excel cells are in the 'General' format, which automaticallyconverts numbers longer than 11 digits into scientific notation.
The simplest way to remove these scientific notations and retrieve the
original numbers is to change the format of these cells (and apply
the Number format).
There are two ways you can quickly change the format
of the cells in Excel—using the Format Cells dialog box and by
using the formatting dropdown in the ribbon.
Hi JohnZiad555 ,
Based on my testing, please try the following method:
1.Go to the Data tab in the Excel.
2.Click get data > From Text/CSV.
3.Select the file and choose import.
4.Select Load or Transform.
But, the Excel cell has digit limitation. The max is 11. If the data beyond 11 digit, the number do not display.
Best Regards,
Wisdom Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
