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 Querys default rounding modes

Lets 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 roundingup value and the roundingdown value?

I believe that most users will say that:

9.445 rounds to 9.45

and

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

ROUNDING IN POWER QUERY

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 thats 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….

ROUNDING IN POWER QUERY

If we select Round… we are asked about the number of decimal places we want to round to.

ROUNDING IN POWER QUERY

We would think that choosing Round… would execute the same rounding method as the ROUND() function in Excel. But thats 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 Querys 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  

and  

9.515 rounds to 9.52 because 9.515 is closer to 9.52 than to 9.50. 

Why Power QueryRoundingMode.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 rounding process thousands or millions of times, we may end up with a final result that may not be in the organization’s best interest. To avoid that and minimize the difference between the real value and the value obtained after summing up many rounded numbers, we can adopt the Bankers Rounding Method that Bill Jelen explained in this video in 2009. The Bankers Rounding Method, also known as ASTM E29 Rounding, corresponds to the default rounding method used by Power Query: RoundingMode.ToEven.

The traditional rounding method corresponds to RoundingMode.AwayFromZero in Power Query.

I can understand that there are good reasons to choose the Bankers Rounding method over the Traditional one as the rounding default method, but certainly there are scenarios where this rounding method makes no sense.

 Why Power QueryRoundingMode.ToEven may be troublesome in some scenarios?  

For example, lets imagine a teacher using Power Query to round the results of a weighted average to determine the students’ final grades. Suppose the teacher expects Power Querys Rounding function to behave the same way as Excel’s ROUND function. Using only the user interface in Power Query, the teacher will not be alerted that the function Number.Round will not perform the same way as ROUND() in Excel. In that case, this can cause students whose weighted average value ends in .5 to see their grade adjusted up or down depending on which whole values the weighted average fell in between. On a scale of 1 to 20, used in Portugal to grade high school students (whole numbers only), a weighted average of 9.5 will be rounded up by Power Query to 10, and a weighted average of 10.5 will be rounded down to 10 as well, instead of 11 as expected.

Final thoughts about Power Querys rounding default mode 

Two things surprise me about the choices made by the Power Query team regarding the Number.Round function.

The first one is the default rounding mode in Power Query not being the same as in Excel (the users expect consistency throughout the tool.) However, and to be fair, I need to mention that inconsistency in rounding methods already existed before Power Query did. VBA also uses the Bankers Rounding method, as explained in Bill Jelens video mentioned above.

The second thing that surprises me is that, at the time I am writing this article, I could not find information about the default rounding mode in Power Query in any of Microsofts documentation for the tool or