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 ...
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."

     

     

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 🙂

       

      • xieyifan2008's avatar
        xieyifan2008
        Frequent Visitor
        6 years ago

        Thanks for your help.