Is there a secret parameter for rounding numbers in Excel PowerQuery?

Question

Please tell me how to round numbers correctly in Excel PowerQuery?

Type "0.925" into Excel.
Play with the toolbar, and Decrease deimals, and the number goes to 0.93
Try a formula: = Round(0.925,2) and you get 0.93

Now try it in PowerQuery

Amount = 0.925
Round([Amount],2) = 0.92

What secret parameter must I use to do the calculation correctly?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ1NzU01bM0MlWKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"RAW Number" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"RAW Number", type number}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Round", each Number.Round([RAW Number],2)),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "FancyRound", each Number.Round([RAW Number], 2, RoundingMode.Up))
in
    #"Added Custom2"

So this query above, in Excel doesn't work

But in Power BI it does.

Ron Rosenfeld · Accepted Answer · 2023-10-09 01:58:52Z

Power Query defaults to round to even for tie-breaking, which is the same as VBA. There are many methods of tie-breaking which have the goal of minimizing bias in the result. Power Query has some of the methods, but by no means all of them.

Excel's default rounding mode is to Round Away from Zero

Here is a function which demonstrates the behavior of the various rounding modes, in Excel Power Query:

//NAME this query "fnDemoRoundingMode"

(n as number, d as number)=>
   Table.FromColumns(
        {
            {"Away from Zero","Down","To Even","Toward Zero","Up"},
            {Number.Round(n,d,RoundingMode.AwayFromZero),
             Number.Round(n,d,RoundingMode.Down),
             Number.Round(n,d,RoundingMode.ToEven),
             Number.Round(n,d,RoundingMode.TowardZero),
             Number.Round(n,d,RoundingMode.Up)}
        },
            type table[Mode=text, Rounded=number])

Here are the results using 0.925 rounded to two digits

= fnDemoRoundingMode(0.925,2)

There are related functions such as Number.RoundUp and others which may also be useful in certain circumstances.
And if you are rounding to an Integer, you can try Int64.From which also takes the RoundingMode arguments and will convert text strings to numbers with an optional culture argument.

davidebacci · Accepted Answer · 2023-10-08 21:14:16Z

1

Yeah, this is an annoying default.

Number.Round([Column1],2, RoundingMode.AwayFromZero)

Returns the result of rounding number to the nearest number. If number is null, Number.Round returns null.

By default, number is rounded to the nearest integer, and ties are broken by rounding to the nearest even number (using RoundingMode.ToEven, also known as "banker's rounding").

However, these defaults can be overridden via the following optional parameters.

digits: Causes number to be rounded to the specified number of decimal digits. roundingMode: Overrides the default tie-breaking behavior when number is at the midpoint between two potential rounded values (refer to RoundingMode.Type for possible values).

https://learn.microsoft.com/en-us/powerquery-m/number-round

edited Oct 8, 2023 at 21:14

answered Oct 8, 2023 at 21:09

davidebacci

31.4k4 gold badges20 silver badges48 bronze badges

3 Comments

Maxcot Over a year ago

Thanks... I tried all 5 examples ... none of them work. To be clear, this is Power Query on Excel, not PowerBI.

davidebacci Over a year ago

The second line in my answer does exactly what you want. I confirmed it in Excel before posting.

davidebacci Over a year ago

Number.Round([Column1],2, RoundingMode.AwayFromZero

Collectives™ on Stack Overflow

Is there a secret parameter for rounding numbers in Excel PowerQuery?

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related