Floating point issue

[mahenkj2]

Hi,

I have a table in Excel sheet, column name is Distance, it has decimal number in it. In power query, I want to transform it by first making it integer/whole number and then multiply it with a factor, say 2.1.

When I see the real value in the query pane, whole number is really whole number without any decimal, but after multiply it with 2.1, it shows some decimal number but digits are increase very much, like 12*2.1 = 25.2, but it shows 25.200000000000003

I attach sample excel sheet and .pbi file for reference.

https://drive.google.com/drive/folders/1ONZwVbetCKbAIyfiKw3R_a1BKTfbqXx0?usp=sharing 

After checking several post on this matter, it seems a floating point issue. If power query can not handle this thing, I have no option but to make these columns in source tables, but before that I want to get support on this forum. Thanks. 

[Anonymous]

You could probably use Double.From([NumberColumn])

--Nate

[mahenkj2]

Thanks. I did not think to round the output, instead was seraching for this just not to happen.

[Vijay_A_Verma · Accepted solution]

The way floating point is handled in Excel, in the same way it has to be handled in PQ as well. The solution is Rounding the number in both apps. You can use Number.Round for this. Your second last step which is Added Custom1, should be this

= Table.AddColumn(#"Changed Type1", "standard", each Number.Round([#"dist@2.1"]*2.1,1))

You can also run Rounding by using Transform - Rounding - Round as your last step on your last column.