Power Query’s Round Function

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:

SNAGHTML20a5291b

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:

image

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:

image

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:

image

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:

image

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!

image

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 TowardsZero and end up with an error!  At any rate, the results:

image

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.

14 thoughts on “Power Query’s Round Function

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

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

  3. Pingback: Excel Roundup 20140922 « Contextures Blog

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

  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?

  6. 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!

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

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

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

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

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

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

If you have a comment or question about the blog post content, please feel free to post it here. If you need help adapting this solution to your own needs, please post in our free help forum.

Your email address will not be published. Required fields are marked *