Forum Discussion
Power Query - merge based on compensation - issue with trailing decimals
I want to merge info together in Power Query based on Compensation. One table has a number listed as 2764.72 and the other has 2764.72000000003. How do I get rid of the trailing digits. both tabs have the format listed a 1.2.
3 Replies
HelloLhansen435,
This behavior is caused by floating point precision. A value that displays as 2764.72 may internally be stored as 2764.72000000003, so Power Query treats them as different numbers during a merge.
A common solution is to round the column in both queries before merging. In Power Query select the Compensation column, go to Transform, choose Round, and round to 2 decimal places.
You can also apply this using M code: Number.Round([Compensation], 2)
Microsoft documentation:
https://learn.microsoft.com/powerquery-m/number-roundAnother option is to change the data type to Fixed Decimal Number or create a rounded helper column in both tables and merge using that column instead.
Set the data type for both as Currency (i.e. the $ sign). That rounds all amounts to two decimals.
Just in case, Currency rounds to four decimals but shows only two.
