Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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."
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."
Thanks for your feedback.
It's bacause of the precision of binary. I understand now.
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
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.
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
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
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 🙂
Thanks for your help.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
52 | |
21 | |
12 | |
11 |