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

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

**.) Then, we choose one of the three options, “Round Up,” “Round Down,“ and “Round…“.**

*Add Column*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

and

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 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 “Banker‘s Rounding Method“ that Bill Jelen explained in this video in 2009. The **Banker****‘****s 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 “Banker‘s 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 Query****‘****s ****RoundingMode.ToEven**** may ****be ****troublesome**** ****in some scenarios? **

For example, let‘s 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 Query‘s 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 Query‘s 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 Banker****‘****s Rounding method****,**** **as explained in Bill Jelen‘s 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 Microsoft‘s documentation for the tool or