Why is power query adding extra numbers to data from excel?

[jdaily83]

For instance, the excel file which is the data source states 31800044.0307 but in power query it's showing 31800044.030699998.  both data types are text in excel and in power query. this is occurring for thousands of rows so I really don't want to have to go through and replace values 1 by 1. I need these to be correct because they're project numbers that I need to match the project unique identifer table. 

Please help

[jdaily83 · Accepted solution]

Issue was with the file, once the report was opened in xls it tried to transform the project number and was causing the error. I have pulled the original files in using text/csv

[v-cgao-msft]

Hi @jdaily83 ,

Based on testing, I didn't get the same results as you. Below are the test steps.

First create the [project numbers] column in excel, select this column, and make sure this column is in text type.

Connect to the data source and transform the data.

Make sure the data type of the [project numbers] column is text. Close and apply

Results have not changed.

Excel supports setting data types by cell, the same column may have different data types. And when you select the entire column, it will not display which data types are included. It is recommended that you select the [project numbers] column and reset it to the text type and save.

Best Regards,
Community Support Team_Gao

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems with it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[jdaily83]

Thank you for your feedback, I checked both the excel file for the fields and confirmed that they are saved as text. I also checked the data type in Power Query and confirmed that it's saved as text. Please help as I can't use this column as a unique identifier/relationship

[v-cgao-msft]

Hi @jdaily83 ,

Would you mind providing an Excel file with no private information? I would like to test the [Project#] column in Power Query Editor, thanks in advance.

How to provide sample data

Best Regards,
Community Support Team_Gao

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems with it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[jdaily83]

please note I also tried to create a new query in hopes that I altered something in one of the steps but it pulled in with the same data and all of the extra numbers.

[jdaily83]

Please let me know if you can access the below

https://1drv.ms/x/s!AnS3coMvk-0jgbFMbdtW7oOqY4f7sQ?e=KuoTFs

[v-cgao-msft]

Hi @jdaily83 ，

After testing, perhaps the problem lies in the data of the EXCEL workbook. This is beyond my knowledge and you can ask questions here.

Microsoft Excel Community

In the meantime, I will continue to look for the cause of the problem, and I will get back to you as soon as there is progress.

Best Regards,
Community Support Team_Gao

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems with it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[jdaily83]

Thank you, I will post with them. In the meantime I suppose doing a replace values may help, a lot of them have similar extra digits so hopefully that will eliminate it from the thousands of ones that aren't matching up. 

[v-cgao-msft]

Hi @jdaily83,

Is this problem solved? I found a post today documenting a similar issue, hope this helps you.

Why Excel show value on CELL not same as FORMULA BAR - Microsoft Community

Best Regards,
Community Support Team_Gao

[amitchandak]

@jdaily83 , the Decimal number does approximation (Binary to decimal conversion). So you can see those differences. In Column tools limit the decimal places, You will see same output

[jdaily83]

Thank you for your response. However, since these are project numbers some of them may be 123456.01 or 123456.002  or 123456.1  so I cannot just decrease the decimal to the same amount for the entire column.  Is there another solution?