There are two things about Power Query and Excel that I want to tell you about in this post. Bill Jelen and I had a chat about these topics, and you can check the full discussion on his YouTube channel (and if you are not a subscriber yet, please consider subscribing and hitting that notification bell while you are there!)
Excel and Power Query‘s default rounding modes
Let‘s say I have a set of numbers that I want to round to two decimal places. What is the rule that you usually use to round decimal numbers? Which law would you apply to round a number that is precisely in the middle between the rounding–up value and the rounding–down value?
I believe that most users will say that:
9.445 rounds to 9.45
9.515 rounds to 9.52.
Why? Most likely because that is how your teacher taught you at school. He or she might have explained to you that we do it like that because that‘s the convention adopted by everyone. I will call this the “traditional rounding method.“
If we use the function ROUND() in Excel, the “traditional rounding method” is the rounding method applied.
However, if we use the Rounding button in Power Query’s Transform or Add Column tabs, we may get surprised by the results. For me, it was surprising, a couple of years ago, when I used the Rounding feature for the first time and then checked the results. I expected Power Query and Excel to have consistent default behaviours, but that‘s not what happens regarding default rounding methods in both tools.
Let‘ see an example.
To round the values in a column in Power Query using the user interface, we select that column, and we click the button Rounding (available in both tabs Transform and Add Column.) Then, we choose one of the three options, “Round Up,” “Round Down,“ and “Round…“.
If we select “Round…“ we are asked about the number of decimal places we want to round to.
We would think that choosing “Round…“ would execute the same rounding method as the ROUND() function in Excel. But that‘s not the case, and the results of function ROUND() in Excel and function Number.Round() in Power Query are not the same for all the scenarios. The image below shows some examples and highlights in orange when Excel and Power Query‘s default rounding behavior to round to two decimal places do not generate matching results.
What is happening here? It turns out that Power Query‘s default rounding mode is “ToEven“ and that consists of rounding to the nearest even in the cases where the number to round is exactly in the middle between the Rounding Up and the Rounding Down values.
Using the two examples mentioned above,
9.445 rounds to 9.44 because 9.44 is closer to 9.445 than 9.446
9.515 rounds to 9.52 because 9.515 is closer to 9.52 than to 9.50.
Why Power Query‘s RoundingMode.ToEven may make sense in some scenarios?
When we round numbers, we introduce an error to our calculations when comparing the final results we get to the one we would get calculating with the real and exact original values.
Except for very rare and special occasions, I always use the Excel ROUND() method and never questioned this rounding procedure much. I simply accepted it. But if we think carefully, the rounding method that always rounds up in the cases where rounding up or down would skew the original value in the same absolute amount, will continuously add up positive errors for similar rounding situations. In a business scenario, for example, where we do this