Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Ng_Lan
New Member

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 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 

Capture.PNG

 

Could anyone help me fix this issue?

Thank you,

Lan

 

1 ACCEPTED SOLUTION
m13eam
Resolver III
Resolver III

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.

m13eam_0-1666352942159.png

 

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!

View solution in original post

2 REPLIES 2
Ng_Lan
New Member

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
Resolver III
Resolver III

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.

m13eam_0-1666352942159.png

 

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!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors