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!

Ng_Lan's avatar
Ng_Lan
New Member
3 years ago

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.