Forum Discussion

xieyifan2008's avatar
xieyifan2008
Frequent Visitor
6 years ago
Solved

Strange Data Type Conversion from Number to Text

Hi,

I have a table in Excel file and all the cells are General data type.

When I import to Power BI through query and change data type to Text, the result is strange.

Can someone help me understand if this issue is a bug or I did something wrong?

 Thanks.

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    Hi xieyifan2008 ,

     

    I got response from PG:

    "This is by design. Numbers in Excel use floating point, so certain values can't be represented with full precision.

    We apply the same display rounding that Excel uses when rendering the preview tables that contain numbers. But if you click on a cell you can see the full (non-rounded) value.

    If you convert the column to Text (as this customer did), you get the full non-rounded value."

     

     

8 Replies

  • Stachu's avatar
    Stachu
    Community Champion
    6 years ago

    in Excel, if you go to cell A2, the value in the formula is 4.1, or similarly to what you see in PowerQuery 4.099999...? If you have formula there (e.g. result of division), then copy the cell and paste as values to see what is the result exactly.

    I assume you will see 4.099999... in the formula bar, even though it will display in the Sheet as 4.1

     

    Cell "type" in Excel is actually just a format, and does not affect what is stored in the cell, only the way it's displayed (e.g. Date type is actually stored as Integer, etc.)

    So in my opinion PowerQuery is showing you the actual value, while Excel shows a formatted value with 1 decimal (in the sheet, you should see the actual value without format in the formula bar). 

     

    Now you can either

    • format the column in DAX so that the value will stay the same but will look like 4.1
    • round the values (it may produce different aggregate values like SUM)
    • xieyifan2008's avatar
      xieyifan2008
      Frequent Visitor
      6 years ago

      Thanks for reply.

      All the numbers are input manually. No formula involved.

      Same issue happened while using Power Query in Excel environment.

      Microsoft Excel for Office 365 (16.0.11929.20586) 64bit

      Power BI (2.78.5740.721) 64bit

      • v-frfei-msft's avatar
        v-frfei-msft
        Community Support
        6 years ago

        Hi xieyifan2008 ,

         

        Thanks for pointing it out, I can reproduce it by using the same version of Power bi desktop as yours. I have reproted it to PG. Once fixed, will let you know 🙂

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi xieyifan2008,

     

    I think it is because Power Bi is automatically detecting the data type of the column. Can you try disabling the below highligted options and see if it helps..

     
     

    Let me know if this works..

     

    Thanks,

    Sneha

    • xieyifan2008's avatar
      xieyifan2008
      Frequent Visitor
      6 years ago

      Thanks for your suggestion.

      Unchecked two automatic options. In Power Query, there is just no automatic type change. When I insert the type change from any to text, it keeps the same result.

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    6 years ago

    Hi xieyifan2008 ,

     

    I got response from PG:

    "This is by design. Numbers in Excel use floating point, so certain values can't be represented with full precision.

    We apply the same display rounding that Excel uses when rendering the preview tables that contain numbers. But if you click on a cell you can see the full (non-rounded) value.

    If you convert the column to Text (as this customer did), you get the full non-rounded value."

     

     

    • xieyifan2008's avatar
      xieyifan2008
      Frequent Visitor
      6 years ago

      Thanks for your feedback.

      It's bacause of the precision of binary. I understand now.