Strange Data Type Conversion from Number to Text

[xieyifan2008]

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.

[v-frfei-msft · Accepted solution]

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]

Thanks for your feedback.

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

[Anonymous]

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]

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.

[Stachu]

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]

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]

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]

Thanks for your help.