Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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

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

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

11 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , 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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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?  

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi Anonymous ,

         

        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