Forum Discussion
Excel is adding extra digits behind the scenes for a report
dailyjm The project numbers you see in Excel seem to be real numbers with many decimals, but formatted to show only three decimals. If you need to pull these into Power BI as numbers with exactly three decimals, you could round them in Excel first. But you can also do the rounding in Power BI by transforming the numbers column to numbers rounded to three decimals.
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.)
