Forum Discussion

Ng_Lan's avatar
Ng_Lan
New Member
3 years ago
Solved

Power Query lost decimal values in numbers

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 co...
Need Help
  • m13eam's avatar
    m13eam
    3 years ago

    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!

m13eam's avatar
m13eam
Resolver III
3 years ago

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!