Helpful Secrets about ROUNDing in Power Query

March 10, 2021 - by Bill Jelen

Power Query is the data cleansing tool built in to Excel and Power BI. Although it is built in to Excel, it is important to remember that Power Query is not maintained by the Excel team. The Power Query architects are coming from the SQL Server Analysis Services world. There are several cases where the default behavior in Power Query does not match the behavior in Excel. Rounding numbers is one of those cases. As of March 2021, the differences are not being documented by Microsoft.

After recording an episode of the MrExcel Podcast with Excel MVP Celia Alves, I wanted to lay it all out while it is fresh in my mind. Thanks to Celia for filling in a lot of the details on Power Query’s rounding. Be sure to check out her article on Power Query rounding at Rounding in Power Query – The default rounding mode and the binary-decimal conversion issue in Excel.

Where Excel’s Rounding is Wrong

First, I don’t want to blame this on the Excel team. Excel was introduced in 1985 and they already had two formidable competitors. Lotus 1-2-3 was the dominant spreadsheet and VisiCalc had been the pioneer. Running originally on a Mac, the original Excel was horribly slow. It would take another 10 years before Microsoft would finally catch up and overtake Lotus. During those years, more than 80% of the business world was using Lotus 1-2-3. If Excel wanted to vanquish Lotus, they had to make sure that Excel calculated the same way as Lotus. So, let’s not say that Excel is rounding incorrectly. Let’s say that market pressures forced Microsoft to make the same mistakes that Lotus or VisiCalc had made.

Excel has a very elementary take on rounding. I call it “Elementary” because it is the same method you learned in elementary school, probably in third grade: If you are rounding to a whole number, decimals from .1 to .4 round down because that is the closest integer. Decimals from 0.6 to 0.9 round up because that is the closest integer. A decimal of 0.5, though, is a tie. It is equidistant to the number above and the number below. You, and I, and every other person on the planet were taught that 0.5 rounds towards the higher number.

We didn’t question this when we learned it, after all, we were eight. If you did ask the teacher why we rounded up, he or she would have said that it is the convention that everyone agrees upon. That teacher was probably counting on the fact that your parent did not work for ASTM International. Formerly known as the American Society for Testing and Materials, this think tank is an international standards organization that develops and publishes voluntary consensus technical standards for a wide range of issues.

Why is the “Always round 5 up” rule a problem? If you keep doing it over millions of data points, you will introduce a slight upwards skew to the numbers. Assuming you are doing rounding to create a simple approximation of the data, you should want the total of your rounded numbers to be very close to the total of your original numbers.

As I documented in a 2009 video (ASTM E29 Rounding or Banker's Rounding), if you take 1 million numbers with exactly one digit after the decimal point and use Lotus’s ROUND function that is built into Excel, you will detect a 0.09% upwards skew in the numbers. In that test, I showed how the original numbers totaled $55 Million and the rounded numbers totaled $50,000 higher. So, if you are a bank and are rounding millions of transactions, it can be costly over time.

(Before you freak out, it is rare to have only a single decimal point in real life. Here in the US, transactions are processed in dollars and cents, so you always have 2 digits after the decimal point. This means less ties (only 1 out of 100 transactions ends at exactly fifty cents) and less error. Using Excel’s ROUND function over 1,000,000 numbers with two decimal places and the $50K upwards skew becomes a $5K upwards skew. With three decimal places, it is further reduced to $290.)

The pros at the ASTM thought about this problem. They proposed that half the time, .5 should round up and half the time it should round down. How do you make sure that half of the ROUND operations go the other way? They suggested that you should always round a 5 towards the even integer. So a number like 1.5 would round up to 2 but a number like 4.5 would round down to 4. It is a very small change, but in my 2009 video, adopting this method (using a combination of IF and MOD) reduced the +$50,000 error to -$200. It really makes a difference. This standard is known as the E-29 Rounding at ASTM. If you want some bedtime reading, here is the type of thorough analysis that ASTM puts into a simple topic like rounding.

As an aside, there are competing standards at ASTM. They have an ASTM D6026 rule that rounds 5 up, just like Lotus did.

ROUND in Excel, VBA, and Power Query

As shown above, Excel offers the ROUND function. It rounds a final digit of 5 to the next highest number for positive numbers and to the next lowest number for negative numbers. While many people say that Excel rounds up to the next highest number when there is a tie, your math teacher would point out that rounding -2.5 to -3 ends up at a lower number. So, the actual phrase is that “Excel rounds AWAY from Zero when there is a tie.”

Excel VBA offers a ROUND function. Since at least 2009, the ROUND function in VBA differs from the Lotus 1-2-3 rounding methodology used in Excel. The ROUND function in VBA follows ASTM E-29 and rounds towards the even integer when there is a tie.

The M language in Power Query offers many rounding schemes (discussed below), but the important thing to know is that if you simply use the Rounding drop-down menu on either the Transform or Add Column tabs in the Power Query user interface, you will be getting the ASTM E-29 rounding (also known as Banker’s Rounding). This means that numbers rounded by Power Query and numbers rounded by Excel will differ when there is a tie. Excel’s ROUND always rounds away from zero. The default method used by Power Query is to resolve ties by rounding towards even.

As is often the case in Power Query, the user interface in the editor offers only a subset of what you can do by editing the M code. With a few edits to M, you can make Power Query round like Excel.

In the figure below, use Add Column, Rounding...

Your only option in the Power Query user interface is to enter the number of decimal places. (Even this question should be optional, as the Power Query documentation says that if you are rounding to an integer, you don’t have to specify the number of decimal places.) Another fun fact: if you want to round to the nearest 10, you can specify -1 digits of precision. For rounding to the nearest thousand, specify -3 here. For rounding to the nearest million, use -6 here.

The default results from Power Query differ from Excel. The two rows circled in red show the data where resolving ties by rounding towards even differs from the Excel ROUND function.

One of my top 10 favorite things about Power Query is that you are secretly writing M code by simply using the Power Query interface. You use an Excel-like ribbon to clean your data and Power Query generates the M programming code to replicate your steps next time. Provided you choose View, Formula Bar in Power Query, you can see the code generated for each step. (Alternatively using View, Advanced Editor to see the code for every step.)

When you use Add Column, Rounding, Round, 0 in Power Query, the equivalent M code is:

Number.Round([Number], 0)

The Secret Power Query Rounding Options

Nothing in the Power Query editor ribbon will reveal this, but the documentation reveals that there is a secret optional third argument to control how ties are resolved.

Number.Round(number, optional digits, optional roundingMode)

If you are using Microsoft 365, all you need to do is to click after the 0 in the Number.Round function in the formula bar, type a comma, and start to type Rou. The intellisense will show you the secret options.

Here is the quick guide to those options to resolve ties:

• RoundingMode.AwayFromZero matches the behavior in Excel. 2.5 rounds to 3 and -2.5 rounds to -3.
• RoundingMode.Down always resolves ties by rounding to the lower number. 2.5 rounds to 2 and surprise... -2.5 rounds to -3. This is different than Excel’s ROUNDDOWN function. If you are looking for the equivalent formula in Excel, it would be =IF(MOD(A2,0)=0.5,FLOOR.MATH(A2,1),ROUND(A2,0)).
• RoundingMode.ToEven is the Power Query default. Ties are resolved by rounding towards the even number.
• RoundingMode.TowardZero handles ties by rounding towards zero. 3.5 rounds to 3. -3.5 rounds to -3.
• RoundingMode.Type generates an error. The documentation for this option is missing. I am not really sure what it does. I want to go out on a limb and say that it should not be in the list, but I am sure there is some double-secret use for it.
• RoundingMode.Up resolves ties by rounding to the higher number. 2.5 rounds to 3. 3.5 rounds to 4. But, be careful: -3.5 rounds to -3. The equivalent function in Excel is a crazy combination of IF, MOD, CEILING.MATH and ROUND: =IF(MOD(A2,0)=0.5,CEILING.MATH(A2,1),ROUND(A2,0)).

Simplify All Of This: How to Get Power Query to Round like Excel?

Here are the steps:

1. Start with a column of numbers in Power Query
2. Select Add Column, Rounding..., 0, OK
3. Click in the Power Query formula bar, just after the 0 in Number.Round
4. Starting typing Rou
5. Select RoundingModeAwayFromZero from the intellisense
6. Click away from the formula bar.

At this point, the new column will be rounding just like Excel.

Other Rounding Options in Power Query

It is important to realize that the rounding modes in the list above are only used when there is a tie. They let you decide is 2.5 is rounding towards 2 or towards 3. They won’t have any impact on 2.1 which Number.Round is always going to round to 2.

As usual, there are more options available in Power Query than in Excel.

Number.RoundDown

If you want to always Round Down, use the Number.RoundDown function. You can get here by choosing Add Column, Rounding, Round Down. For positive numbers, Excel’s =ROUNDDOWN and Power Query’s Number.RoundDown act the same. But for negative numbers, Excel’s ROUNDDOWN function rounds towards zero. In Power Query, Number.RoundDown(2.999,0) will round to 2. Number.RoundDown(-2.9999,0) will round down to -3. Contrast this with =ROUNDDOWN(-2.9999,0) in Excel which generates -2.

The actual Excel equivalent of Power Query’s Number.RoundDown is =FLOOR.MATH. The second argument in FLOOR.MATH is significance, so in Excel, you would write =FLOOR.MATH(A2,1) to round down to the next lowest integer.

Other Excel equivalents of Number.RoundDown are =INT(A2) and the legacy =FLOOR(A2,SIGN(A2)). Avoid using FLOOR, as =FLOOR(-2.1,1) behaves in an unexpected manner. There is a reason Microsoft replaced FLOOR with FLOOR.MATH.

Number.RoundUp

If you want to always Round Up, use the Number.RoundUp function. You can get here by choosing Add Column, Rounding, Round Up. For positive numbers, Excel’s =ROUNDUP and Power Query’s Number.RoundUp act the same. But for negative numbers, Excel’s ROUNDUP function rounds away from zero. In Power Query, Number.RoundUp(2.1,0) will round to 3. Number.RoundUp(-2.1,0) will round up to -2. Contrast this with =ROUNDUP(-2.1,0) in Excel which generates -3.

The actual Excel equivalent of Power Query’s Number.RoundUp is =CEILING.MATH. The second argument is significance, so in Excel, you would write =CEILING.MATH(A2,1) to round up to the next highest integer.

Again, avoid the buggy =CEILING function in Excel. It is only there for backward compatibility.

Number.RoundAwayFromZero is only available by writing M

If you need the equivalent of Excel’s =ROUNDUP function, use =Number.RoundAwayFromZero(2.1,0). You can’t currently create this function in the Power Query user interface. You could choose to Round and then edit the function from Number.Round to Number.RoundAwayFromZero in the formula bar.

Number.RoundAwayFromZero in Power Query is the same as the ROUNDUP function in Excel. In both Excel and Power Query rounding 2.1 will give you 3. And rounding -2.1 will give you -3.

Number.RoundTowardZero is only available by writing M

If you need the equivalent of Excel’s =ROUNDUP function, use =Number.RoundTowardZero(2.999,0). You can’t currently create this function in the Power Query user interface. You could choose to Round and then edit the function from Number.Round to Number.RoundTowardZero in the formula bar.

Number.RoundTowardZero in Power Query is the same as the ROUNDDOWN function in Excel. In both Excel and Power Query rounding 2.9 will give you 2. And rounding -2.9 will give you -2.

Other Rounding Options in Excel and their Power Query Equivalents

Excel has an embarrassing variety of badly named rounding functions. Some they inherited from Lotus 1-2-3, but others came with the addition of the Analysis ToolPak add-in to Excel. Some of those functions have Power Query equivalents.

Actually, all of Excel’s rounding functions make sense for positive numbers:

=ROUNDUP(2.1,0) rounds up to 3

=ROUNDDOWN(2.999,0) rounds down to 2

=CEILING(2.1,1) rounds up to 3. You could also use this function to round up to the next multiple of 5:
=CEILING(12.1,5) would round to 15. If you never encounter negative numbers, you won’t feel the need to go to the newer CEILING.MATH. But everyone will encounter some negativity sometimes, so you should be using =CEILING.MATH(2.1,1) to be safe.

=FLOOR(2.9,1) would round down to 2. You could round down to the next 100 using =FLOOR(574,100) to get to 500. If you have upgraded beyond Excel 2003, you should use FLOOR.MATH instead of FLOOR.

=INT(2.1) truncates the decimals and gets you to 2.

=MROUND(22.8,5) will round to the nearest multiple of 5 and will return 25.

But for negative numbers, Excel will give your Math teacher some serious heartburn

If you enter =ROUNDUP(-2.1,0) in Excel, you will get -3. If you are in a bar, casually discussing Excel, this might seem perfectly reasonable. But if you are a third grader at St Rose of Lima School, Sister Mary Catherine will pull out her Number Line and show you that going “UP” from -2.1 means that you are moving towards the right and the correct answer should be 2.

The behavior of =ROUNDUP in Excel is =Number.RoundAwayFromZero in Power Query.

In a similar fashion, =ROUNDDOWN(-2.1,0) returns -2 even though -2 is more than -2.1. If you would have pointed this out to the developers at Lotus 1-2-3, they would have conceded that they really meant =ROUNDTOWARDZERO. The Power Query people have corrected the Lotus 1-2-3 error and given us Number.RoundTowardZero.

I like to think that Excel’s INT function will quickly truncate the decimals, leaving you with the integer portion of the number. But I am completely wrong. If you want to truncate decimals, you should use the =TRUNC(A2,0) function. For positive numbers, both =INT(2.1) and =TRUNC(2.1,0) will give you 2. But for negative numbers, =INT(-2.1) will take you to -3. =TRUNC(-2,1,0) will take you to -2. So, to be accurate, the equivalent of Excel’s INT function is Number.RoundDown. The equivalent of Excel’s TRUNC function is Number.RoundTowardZero.

The old CEILING and FLOOR should be banned for negative numbers. The proper way to use them was to specify a significance of 1 for positive numbers and -1 for negative numbers. This lead to a situation where CEILING would sometimes round away from zero and sometimes round towards zero.

Ignore CEILING and FLOOR, the newer CEILING.MATH is equivalent to Number.RoundUp in Power Query and Excel’s CEILING.FLOOR is equivalent to Number.RoundDown in Power Query.

Power Query Equivalent of MROUND

Excel has a nice MROUND function that lets you round to the nearest multiple of any number. If you need to round to the nearest 5 or 25, MROUND will do it.

The syntax is =MROUND(Number,Multiple). There is a problem with MROUND and negative numbers. The sign of the Multiple argument must match the sign of the Number argument. Take a look at column B below. The =MROUND(B13,25) returns a #NUM! error because B13 is negative and 25 is positive.

The workaround in Excel is to multiply the Multiple by the SIGN() of the number. The formula in C2 below is =MROUND(A2,25*SIGN(A2))

Given this complication, the Power Query equivalent is almost easier.

From the Power Query editor, choose Add Column, New Custom Column. For the nearest multiple of 25, you would use

=Number.Round([Number]/25,0,RoundingMode.AwayFromZero)*25

Depending on your multiple that you want to round to, you would use that multiple twice in your formula, replacing my 25 with your multiple in two places.

Power Query Equivalent of CEILING.MATH(,5)

The CEILING.MATH function in Excel will always round up to the next multiple of the significance argument.

To mirror this in Power Query, use Add Column, Custom Column and a formula of Number.RoundUp([Number]/5)*5. Note that you would replace both 5’s in this formula with your value for significance.

The results:

There is a variant of CEILING.MATH where you specify a non-zero value as the third argument. This will force Excel to round away from zero. The Power Query formula for this is Number.RoundAwayFromZero([Number]/5)*5

Power Query Equivalent of FLOOR.MATH(,5)

The FLOOR.MATH function in Excel will always round up to the next multiple of the significance argument.

To mirror this in Power Query, use Add Column, Custom Column and a formula of Number.RoundDown([Number]/5)*5. Replace both of the 5’s with your significance.

There is a variant of CEILING.FLOOR where you specify a non-zero value as the third argument. This will force Excel to round towards from zero. The Power Query formula for this is Number.RoundTowardFromZero([Number]/5)*5

Power Query Equivalent of Excel’s TRUNC

The TRUNC function in Excel will truncate a number at a certain number of digits after the decimal place. No rounding takes place. In essence, the TRUNC function is doing what Power Query’s Number.RoundTowardZero is doing.

In Power Query, a custom column formula of =Number.RoundTowardZero([Number],[Digits]) will replicate TRUNC

As an Excel expert and a Power Query novice, I am always annoyed when something in Power Query does not match something in Excel. (I always wonder why Excel’s Text to Columns becomes Power Query’s Split Column.) However, if you dive in and find the specifics, you will often realize that Power Query is offering a richer, more complete set of options. If only the documentation could be updated to fully explain those features.

My sincere thanks to Excel MVP Celia Alves for teaching me how to make Power Query round like Excel. Subscribe to her YouTube channel at: https://www.youtube.com/c/CeliaAlvesSolveExcel

Title Photo: krakenimages on Unsplash