Downloads
Power Query MNumberIntermediate

Number.Round

Rounds a number to a set number of decimal places, using a rounding rule you can choose.

Syntax
Number.Round(number as nullable number, optional digits as number, optional roundingMode as nullable number) as nullable number

Returns: The rounded number, or null when the input is null.

What it does

Number.Round rounds to the nearest value at the digit you choose. The part that surprises finance teams is the default rule. When a number is exactly halfway, Number.Round rounds to the nearest even digit, so 2.5 becomes 2 and 3.5 becomes 4. This is called banker's rounding, and it is also the default in many statistical tools. Many accounting policies and tax rules expect the halfway value to go up, away from zero.

Syntax and arguments

Example

A controller rounds a set of unit amounts to whole dollars. The team compares the two rules on a few halfway values before choosing one for the report.

let
    Values = #table(type table [Amount = number],
        {{2.5}, {3.5}, {2.4}}),
    Default = Table.AddColumn(Values, "DefaultRule", each Number.Round([Amount]), type number),
    AwayFromZero = Table.AddColumn(Default, "AwayFromZero",
        each Number.Round([Amount], 0, RoundingMode.AwayFromZero), type number)
in
    AwayFromZero

The default rule turns 2.5 into 2 and 3.5 into 4, because it moves the halfway value to the even digit. The AwayFromZero rule turns 2.5 into 3 and 3.5 into 4. The value 2.4 rounds to 2 under either rule. The same split happens with decimals: with two places, 0.125 becomes 0.12 under the default and 0.13 with AwayFromZero. The value 0.125 is exact in binary, so the difference is the rule, not floating point noise.

Example: a second, different use

Round a calculated sales tax to cents with AwayFromZero so the tax on the invoice matches the rule the state applies.

Things to watch

In business terms

Invoices, tax calculations and payroll withholding all round to cents, and a half cent rule decides whether the total matches the bank or the tax portal. Setting the rounding mode explicitly keeps the report in line with the policy the auditors will read.

Related functions

All Power Query M functions · View as Markdown · M basics