Power Query lost decimal values in numbers

[Ng_Lan]

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.

Excel dataset 

PBI file 

Could anyone help me fix this issue?

Thank you,

Lan

[Ng_Lan]

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.

[m13eam · Accepted 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!