Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi all,
I have a dataset from Excel file that has number columns mostly containing decimal values up to 2 digits. Many values are 0.01, 0.02, 0.35...
After loading to Power Query, all values are converted to whole number, e.g 0.01 become 0.
I tried to change data type to Decimal Number, or Fixed Decimal Number but could not get those decimal values, they all become 00 after the decimal point.
Please see below image and sample files in the link.
Could anyone help me fix this issue?
Thank you,
Lan
Solved! Go to Solution.
Hi Lan,
The issue appears to be within the "Changed Type" step in PowerQuery. The "Allowed Time", "C(PC)" etc columns are all being set to a Whole Number.
If they are set to "Decimal Number" then it should work as you require.
You mention that you've already done this and it didn't work. However, in PowerQuery, you only see a sample set of rows (the first c.1000). In the Excel file you provided, the first 1000 rows are all genuinely a value of 0.
Therefore, I suspect you had done it correctly, but it needed to be tested against a larger sample (the first value greater than 0 is at row 1248)
I hope this helps!
Hi @m13eam ,
Thanks a lot for having a look.
I did know that the first 1000 rows are 0, hence you might notice from the image I posted (and from query step) I filtered the rows of zero values in Allowed Time column to look at rows with values and compared it with data in Excel.
From your comment, I notice what I did wrong is that I changed Type to decimal after Power Query has automatically Change Type following Promoted Headers step. So, the solution should be editting data type in the M code of Advanced Editor. I should have not edited using the PQ interface.
Thank you for pointing it out.
Lan.
Hi Lan,
The issue appears to be within the "Changed Type" step in PowerQuery. The "Allowed Time", "C(PC)" etc columns are all being set to a Whole Number.
If they are set to "Decimal Number" then it should work as you require.
You mention that you've already done this and it didn't work. However, in PowerQuery, you only see a sample set of rows (the first c.1000). In the Excel file you provided, the first 1000 rows are all genuinely a value of 0.
Therefore, I suspect you had done it correctly, but it needed to be tested against a larger sample (the first value greater than 0 is at row 1248)
I hope this helps!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
68 | |
62 | |
21 | |
18 | |
12 |