Rounding in Power Query – The default rounding mode and the binary-decimal conversion issue in Excel

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

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

and  

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

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.

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.

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 anywhere in the user‘s interface as shown above. This means that many applications in Power Query around the world might be using a rounding mode different from the one intended. There is no easy way for users to understand the default rounding mode applied by Power Query and the rounding options available.

I have been aware of Power Query‘s default rounding mode for a long time, only because I was lucky enough to have stumbled upon Ken Pul‘s blog post about this same topic.  

The picture below shows the results we get when rounding the set of numbers used above, using all the rounding modes available for the Number.Round function in Power Query. The cells highlighted in orange show the cases where the rounding mode’s result does not match Excel‘s ROUND() function result. We can see that for the numbers in the data, the RoundingMode.AwayFromZero is the one that matches The ROUND function in Excel. 

Please read Ken Pul‘s post mentioned above to get more information about each of the rounding modes in Power Query. For all the rounding methods in Excel and Power Query and the correspondence between them, check this comprehensive article by Bill Jelen.

The easiest way to do that is to use the button Rouning in the user interface and then edit the M code generated in the formula bar to indicate the rounding mode of our choice for that operation.

Now… The purpose of this blog post so far has been to help more Power Query users to be aware of the default rounding mode used by that tool. But there is more to this rounding story, and that’s what I’ll tell you now.

As I said, I already was aware that we need to specify the rounding mode in Power Query if we do not want to use the default RoundingMode.ToEven.

But a few days ago, after rounding in Power Query using RoundingMode.AwayFromZero with two decimal places, I took a quick look at the results and noticed something odd. I need to say that the data was being imported from another Excel file, type .xlsx, with Power Query into Excel.

Let‘s see the following two examples:

2548.125 shows rounding to 2548.12

22723.505 shows rounding to 22723.51

After investigating in a couple of places, I ended up clicking individual cells in the Power Query editor, and what I uncovered was bizarre…

EXAMPLE 1:

Value in a cell in the data source (Excel Table): 2548.125

Imported value showing in Power Query editor‘s table: 2548.125

Value showing when selecting the cell in Power Query editor: 2548.1249999999995

Value after rounding with two decimal places, RoundingMode.AwayFromZero: 2548.12

EXAMPLE 2:

Value in a cell in the data source (Excel Table): 22723.505

Imported value showing in Power Query editor‘s table: 22723.505

Value showing when selecting the cell in Power Query editor: 22723.5050000000001

Value after rounding with two decimal places, RoundingMode.AwayFromZero: 22723.51 

I was confused. I thought that Power Query was modifying the values brought from the data source and then rounding those numbers instead of the original ones resulting in an inconsistent rounding process.

I wrote to my MVP colleagues about this, asking if someone had an explanation for this phenomenon. It turns out that Power Query is not the one to blame. Excel is!

Jan Karel Pieterse, MVP and VBA expert, unveiled that the odd numbers 2548.1249999999995 and 22723.5050000000001 that show in Power Query‘s editor when we point to each cell are the values stored by Excel in the XML code of the data file. And Power Query reads from it when importing data from an external Excel file.

The rounding issue , as Jan explained me, is due to a basic flaw in computers called the floating point rounding issue. It can cause the same number to be stored differently depending on the machine. For example, when Jan tested the same numbers on his end, he got 22723.505 rounded to 22723.5050000000001 but 2548.125 remained stored at facial value.

So, yeah… The shocking news here is that the values stored by Excel may not match precisely the values that we type in a cell. Excel uses binary-decimal conversion to store the information and that causes this mismatch for some values. And, yes… when we import data from a separate Excel file with Power Query, it will read the values in the XML code and not the values initially typed in the cells.

Bill Jelen has another video showcasing the binary-decimal conversion issue in another situation.

As a final note, the issue with Power Query importing wrong values from the XML code seems to only happen when the data is on a file different from the one that holds the query and the data source is an Excel file type .xlsx or .xlsm. Excel files type .xls or .xlsb do not have this issue, but they might have others.

Testing with the first data set in this article, importing it with Power Query from a separate file, we get the following result:

I am curious if other people came across this issue before. If you did, what are your thoughts about all this?

Note: If you are dealing with the rounding issue in Power Query, Rick de Groot’s article Power Query Precision: Avoid Rounding Errors might be helpful.

• Learn how to automate your Excel reports
• Get this ready-to-use Excel solution for 2 and 3 columns of Dependent Dropdown Lists
• Check other posts about Power Query and Excel in this Blog,