The other day I asked one of my co-workers how many ways he knew of to round a number. His answer was one… if it ends in .4 it rounds down and if it ends in .5 it rounds up. My guess is that most people would answer along similar lines, but that's NOT how Power Query's Round function actually works...

Interestingly though, there are a bunch of different ways to round, depending on your needs, and Excel has a bunch of functions to support them: ROUND, ROUNDUP, ROUNDDOWN, FLOOR, CEILING, EVEN, ODD, TRUNC, INT and MROUND.

Power Query also has a bunch of rounding formulas as well but, given that the function names can be somewhat different in Power Query (as we first saw here), I thought it might be interesting to see how to convert each of those functions from Excel to Power Query’s structure.

# Background Setup

To start with, I created a very simple structure: a two column table with some random values in the “Value” column and then rounded them to 2 decimals using the formula =ROUND([@Value],2) The output, after feeding it through Power Query, looks like this:

The blue table on the left is the Excel table, and the green table on the right is the Power Query output. (There is a completed example file available here example file available here.)

# Creating the Round function

I love the ROUND function in Excel. I use it constantly – rounding everything that uses multiplication or division – and pretty much have it burned into muscle memory. So to me this was a logical place to start with Power Query. Naturally, the syntax is just a bit different from Excel though:

Syntax | |

Excel | =ROUND(number,digits) |

Power Query | =Number.Round(value, digits, roundingMode) |

Hmm… we know that the Power Query function will be case sensitive. In addition, it has an extra parameter. The valid options are:

- RoundingMode.Up
- RoundingMode.Down
- RoundingMode.AwayFromZero
- RoundingMode.TowardZero
- RoundingMode.ToEven

Let’s see what we can do with this.

I open up my query, select Add Column, and put in the formula as shown below:

Pretty easy, just Number.Round([Value],2). In fact, it’s so similar to Excel it’s awesome!

So I click OK, save the query, and have a look at my results. And that’s when I notice something isn’t quite right. I’ve added some conditional formatting to the table below so you can see it easily:

# This is the default?

Notice all the numbers that don’t match? Can you spot the pattern? It’s the oddest damn thing I’ve ever seen, to be honest, and I’ve never heard of anyone rounding in this way.

The default “RoundingMode” for Power Query is “Round to Even”. What that means is that if there is a tie in the numbers it will round up or down to the closest even number. So in the case of 1.645, it will round down to 1.64 as that is closer than 1.66. (1.64 is .05 away from 1.645, where 1.66 is 0.15 away from 1.645).

I find this deeply disturbing. I personally think that every user would expect Excel and Power Query’s default rounding methods to line up exactly, and this doesn’t. How serious is this? I’m not sure. I think I’ll let someone from the scientific community ponder that.

# Using RoundingMode.Up

Since the default plainly doesn’t work for us, it looks like it’s time to figure out which of the additional parameters we need. Let’s try adding RoundingMode.Up to see if that will fix it.

I open Power Query again, and added a new custom column with the following formula:

=Number.Round([Value],2,RoundingMode.Up)

And the results are as follows:

Um… uh oh. It seems to work above 0, but below is another matter. That –5.245 is rounding down, not up! (Yes, from a technical perspective I am aware you can argue the words I used, but you get the idea.)

# Using RoundingMode.Down

Now I’d be surprised if this came up with numbers consistent with the Excel formula, but let’s just check it for good measure. The formula is:

=Number.Round([Value],2,RoundingMode.Down)

And the results:

So now numbers greater than 0 get rounded down, where numbers less than 0 are being rounded up (away from zero).

Let’s try another:

## Using RoundingMode.AwayFromZero

Here’s our next option:

=Number.Round([Value],2,RoundingMode.AwayFromZero)

And these results are pleasing!

Look at that… we finally found the one that works!

## Using RoundingMode.TowardZero

We’ve only got one other option we haven’t explored, so we might as well use it too, just for the sake of completeness:

=Number.Round([Value],2,RoundingMode.TowardZero)

For some reason, I’m incapable of typing TowardZero the first time I type this. I always type Toward**s**Zero and end up with an error! At any rate, the results:

# Thoughts

As a tool that is built for the Excel audience, I am having some real difficultly accepting the default parameter for this function in Power Query. I HOPE that this is a bug, and not a design choice, although the documentation would suggest it is the latter. If that’s the case, I think it’s a HUGE mistake.

Excel’s ROUND formula defaults to round away from zero. Power Pivot’s DAX ROUND formula defaults to round away from zero. VBA’s Application.Round function defaults to round away from zero. (As pointed out by Rory Archibald on Twitter, VBA's Round function - without the application. prefix - does use banker's rounding though.)

In my impression, if the Power Query formula holds the same name (at least after the Number. portion) it should return the same results as the Excel function. In fact, I would venture to say that virtually every Excel pro would expect this.

My bigger concern would be that, with one of Power Query’s big selling features being it’s ability to re-shape and process large volumes of data, how quickly will a user realize that the Rounding function they thought they had is NOT working the way they expected? Not good news at all.

I’m curious to hear your impressions. Please leave a comment!

# Want to see if for yourself?

Download the example file with all the formulas already in place.

The VBA ROUND function rounds to even -- unlike the Excel ROUND function. I've been burned by that in the past.

Hi there,

Yes, I updated the blog post to reflect that one. Application.Round (which is what I always used) rounds consistently with Excel and DAX. Round (without the application) rounds to even.

Pingback: Excel Roundup 20140922 « Contextures Blog

On the 8th line in the final table RoundingMode.Down on 0.866 evaluates to 0.87...is that intentional?? Excel ROUNDDOWN gives 0.86 as you would expect...it rounds down!. The same happens for 1.756, or apparently, whenever the final digit is >0.5.

That's final digit >5!

Ok I see, so RoundingMode.Down it's not intended to replicate Excel's ROUNDDOWN, it's just to force a round down where the final digit is 5, rather than round to even. Is that right? If so does anyone know how to round down to the last digit of the specified number of digits i.e. 0.866 => 0.86? .Or even an MROUND equivalent?

Yeah, it's kind of frustrating, isn't it? Have a check of this page and you should be able to find something that will let you replicate it, although you'll have to do some manual math, I'm afraid: https://msdn.microsoft.com/en-us/library/mt253322.aspx

Cheers Ken, yes it did take some basic maths to get an MROUND equivalent solution, for multiples of a decimal (in this case 0.05) this worked:

let

Source = Excel.CurrentWorkbook(){[Name="DECIMAL"]}[Content],

Find2ndDecimal = Table.AddColumn(Source, "2ndDecimal", each [Number]-Number.RoundDown([Number],1)),

MROUND_0.05 = Table.AddColumn(Find2ndDecimal,"MROUND_0.05", each

if [2ndDecimal] = 0 then [Number]

else if List.AllTrue({[2ndDecimal]>=0.03, [2ndDecimal]<=0.07}) then Number.RoundDown([Number],1)+0.05

else Number.Round([Number],1))

in

MROUND_0.05

If there is a more universal solution of rounding to multiples let me know!

This is a great post! I'm trying to round to closest 0 0r 9 in PowerPivot so that the pennies in a retail price end in either 0 or 9. Any ideas on that one?

I think I'd cook up a bit of logic to work out the last digit, then just test it with an if statement, to be honest. There is no power query function that I'm aware of that will let you round to a specific digit.

I just got burned by assuming Power Query rounding works like Excel rounding. I believe the "normal" rounding rule is:

5 or more is rounded up.

4 or less is rounded down.

In Power Query, I selected a column, right clicked, and navigated to Transform > Round > Round... > 2 Decimal Places. This does not follow the normal rule.

It is amazing how Microsoft was not able to get its developers from different products to agree on common functions.

Amen brother. And of course now that it's set this way and has been out in the field, they can't change it. I wish they'd add a an optional parameter to it like VLOOKUP has that just seems to default the wrong way. Something like =ROUND(Number, Decimals, Banker Rouding) with a default of Banker Rounding. They we could just say "Don't forget the ,false to get it to work.

Hi Ken,

FYI, I just checked this in the current version of Power BI Desktop (2.46.4732.581 64-bit (May 2017)) and this still appears to be a "bug". I'm really glad that we have this post of yours to refer to.

I'm using this formula:

= Table.AddColumn(#"Changed Type", "Amount", each Number.Round([Hours]*[Rate],2))

And you can see that the results are still wrong:

http://imgur.com/a/l1RVR

Wrong or not, there's no way they'll change it now. The issue is that someone out there relies on this behaviour and actually does want it, so they can't fix it, even if they wanted to.

Hi Ken,

I agree that the different rounding methods are confusing for the average user. Not only the Round-Function but also the rounding during data type changes uses the method "round to even".

This method has advantages though, because it's less biased than the usual rounding up of 5s. It's therefore the chosen method in the IEEE Standard for Floating-Point Arithmetic (IEEE 754) and also used in other programming languages (e.g. VBA).

see also: https://en.wikipedia.org/wiki/Rounding#Round_half_to_even

https://en.wikipedia.org/wiki/Nearest_integer_function

Hi Ignaz,

Thanks for this. My concern with this is not really so much about confusing the average user, it's more about delivering on expectations. If you ask virtually anyone who is not a programmer how to round, they expect that the rounding up of 5s is the default. This is the method taught in schools around the world from an early age. It may be more technically unbiased, but if 90% of the world doesn't know this and expects a different result, it's a poor choice. Personally I would have rather seen them make the rule to use 5s by default, and add transformations so that you can force it to use the IEEE standards.

I was looking for some rounding functions to get the Quarter from a Date's Month. Dividing the Month by 3 with Excel ROUNDUP() works correctly, but none of the options in this post matches Excel. I've found that PQ Number.RoundUp(), however does match Excel ROUNDUP().

https://msdn.microsoft.com/query-bi/m/number-roundup.

I'm using this because I'm calculating a Fiscal Month. Once I have that I use it to determine the Fiscal Quarter.

Thanks for posting this article. It's fortunate that I knew that there are multiple rounding algorithms so I searched for answers. My fear is that many people aren't aware and the default option isn't the bet choice.

It is annoying that these options exist but they aren't included in the ribbon or the step configuration dialog (Excel 2016). I understand that Microsoft may not want to change the default, but they could change the UI to raise visibility to the options.