Forum Discussion
Excel is adding extra digits behind the scenes for a report
dailyjm Don't really follow. Where do the project numbers come from to begin with?
Can you upload a screenshot showing the number with e.g. three decimals and what's included in the formula bar. For example like this:
The data is coming from an .xls extract from an Oracle based reporting tool. I've tried to change to text & csv to pull in but all end results are always the same. Out of roughly 50,000 projects there are around 5,000 or so that are doing this. Sure, see below:
As you can see in the following screenshot there are many different types of project numbers that have varying decimal places which won't permit me to just round up in Power BI. The below are valid project numbers
dailyjm wrote: ``The data is coming from an .xls extract from an Oracle based reporting tool. I've tried to change to text & csv to pull in but all end results are always the same. ``
As we see in your examples, not all project IDs are numeric; for example, one is 188724-47.LS.
Consequently, the project IDs should be entered as text.
Do not open the csv or txt file directly in Excel.
Instead, save the file to a folder, click on the Excel icon to open Excel, not the file, then import the csv/txt file into Excel using the Text Import Wizard. In Excel 2010, I click Data > From Text and select the file.
In the Text Import Wizard, work your way through the 3 dialog boxes. First, choose the method of column separation (probably delimited). Click Next. Then, select the column separator (probably comma). Click Next. Here is where the real magic is done. Select the column with the project IDs, and select Text under "Column data format". Finally, click Finish.
PS.... I am not familiar with the transfer of data from Oracle to Excel and from Excel to Power BI. If the Oracle project IDs are coded as text, perhaps Oracle can create the Excel file directly without using a csv/txt file. And that case, perhaps Oracle would format the project IDs as text. I don't know.
-----
Possible TMI.... The reason that the number 31900004.001 appears as 31900004.000999998 is because the latter is the correct decimal representation (rounded to 17 significant digits) of the internal binary approximation of the original value.
Usually, we see it as 31900004.001 because Excel displays only up to 15 significant digits, rounded.
I am (pleasantly) surprised that Power BI displays 17 significant digits.
FYI, the exact decimal representation of the binary approximation of 31900004.001 is 31900004.0009999,983012676239013671875 .
(I use period for the decimal point and comma to demarcate the first 15 significant digits.)
JoeUser2004 Thank you for your feedback. This method did help to remove extra digits but it also removed some of the digits that I needed. So now a lot of the ones that are .001 are no longer including the decimals. It's weird because it did not wipe out all decimals, only some.
dailyjm Okay! Thought I could easily fix it, but I can't. Sorry.
- thank you for trying! It's been driving me crazy. The manual fix will be quite time consuming on a monthly basis.
