22 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. That's final digit >5!

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

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

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

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

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

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

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

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

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

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

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

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

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

19. Just to follow up on the first reply you received... VBA does not only use "round to even" (also known as "Banker's Rounding") for it Round function, but it also uses this rounding scheme **everywhere** that rounding takes place (for example, giving a decimal value to the CLng function). If a number is to be rounding for any reason in VBA, it uses Banker's Rounding to do it... with one exception (which I'll get to in a moment). This automatic, behind-the-scenes rounding can produce surprising results sometimes. VBA has an integer division operator (the backward slash \) which divides two integers and then rounds the answer (using Banker's Rounding). The problem is that it divides **integers**, so if you give it a decimal number for the numerator or denominator, it rounds each - using Banker's Rounding - before performing the division. So, if you have this division to perform and use the integer division operator... some people mistakenly think it is a shortcut for Int(numerator/denominator)...

    4.5 \ 1.5

    The answer, according to VBA, is NOT 3, rather it is 2. VBA rounds the 4.5 to the nearest even number 4, then rounds the 1.5 to the nearest even number 2 and then does the division. As I said, all of the automatic, behind-the-scenes Banker's Rounding can get you in trouble. Now, earlier I said there was an exception in VBA to using Banker's Rounding... the Format function. It uses what I think of as "normal rounding" (5 and higher up, the rest lower). As Ken said elsewhere, Application.Round can also be used.

20. Thanks Rick!

21. Interesting.

    For me, it rather confirms my tendency to avoid rounding all together. Before one rounds one has the value to something approaching machine precision (14 s.f. say); after you are lucky to retain 4 or 5 s.f., especially if division is not disallowed.

    I had never heard of 'bankers rounding' but I can see some sense in a world where adding 0.5s is something that is so widely occurring that randomising the 'ups'' and 'downs' is desirable.

    How about "Feel free to round any number" (to the nearest $1,000 if you so choose), but it is your responsibility to keep track of where your rounded off amounts went and demonstrate that it conforms to the contractual requirements.

22. To me, I think the "it is your responsibility" part is implied. But it's not a matter of not understanding that a value has been rounded, it is understanding HOW it was rounded. Did it follow the "Down < 0.5 <= Up" rule the significant majority of humans are used to? Banker's rounding? Round all values up to nearest whole? Down to nearest whole? Truncate decimals? And then with negative numbers, does it round towards or away from zero? There are a ton of different ways to round, each with their own use cases. My biggest argument here is that most people have never heard of banker's rounding, which means it should NOT have been set as the default for PQ. Provide the ability, yes, but the default should be the "Down < 0.5 <= Up" rule with separate options for all the others - like Excel has done and set user's expectations towards.

This site uses Akismet to reduce spam. Learn how your comment data is processed.