Number.Round Function in Power Query M (6 Easy Examples)

If you want to round a number to a whole value or to a set number of decimal places in Power Query, the Number.Round function is what you reach for. It takes a number and gives you back the nearest rounded value. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

Table of Contents

Syntax of Number.Round Function

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

where

number (required, nullable number). The value you want to round. If number is null, the function returns null.
digits (optional, nullable number). How many decimal places to round to. Omit it and you round to the nearest whole number. It can be negative to round to tens, hundreds, and so on.
roundingMode (optional, nullable number). Sets how ties are broken when the value sits exactly halfway between two candidates. The default is RoundingMode.ToEven.

Returns: a number rounded to the requested precision. If number is null, it returns null.

In plain terms, you give it a number and how precise you want it, and it hands back the nearest value at that precision.

Example 1: Round to the nearest whole number

Round a single value to the closest integer by leaving out the digits argument.

Number.Round(7.62)

Result: 8

With no digits given, it rounds to the nearest whole number, and 7.62 is closer to 8 than to 7.

Example 2: Round to two decimal places

Set digits to 2 to keep two numbers after the decimal point.

Number.Round(83.4567,2)

Result: 83.46

The third decimal is a 6, so the second decimal rounds up from 5 to 6.

Example 3: The default uses banker’s rounding

This one trips people up. Round 2.5 with no extra arguments.

Number.Round(2.5)

Result: 2

You might expect 3, but you get 2. The default mode is RoundingMode.ToEven, which sends an exact tie to the nearest even number. Since 2.5 sits halfway between 2 and 3, it goes to the even one, 2.

Example 3b: Force normal round-half-up

To get the round-half-up behavior you learned in school, pass RoundingMode.AwayFromZero as the third argument.

Number.Round(2.5,0,RoundingMode.AwayFromZero)

Result: 3

Now the same 2.5 rounds up to 3. This is the mode to use for money, grades, or anything where a half should always go up.

Example 4: Round a calculated column over a table

Most of the time you are rounding a whole column at once, not a single value.

Say you have a Readings query of temperature readings and you want each one rounded to two decimal places.

Here is the starting data:

Station	TempC
North Ridge	18.3461
Harbor Point	22.8009
Valley Base	9.4925
Summit Lab	31.7783

Add a rounded column with Table.AddColumn, then keep just the columns you want:

let
    Source = Excel.CurrentWorkbook(){[Name="Readings"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"TempC", type number}}),
    Rounded = Table.AddColumn(Typed,"TempRounded",each Number.Round([TempC],2),type number),
    Result = Table.SelectColumns(Rounded,{"Station","TempRounded"})
in
    Result

Number.Round([TempC],2) runs once per row and rounds each reading to two decimals.

The result keeps the station and the rounded value:

Station	TempRounded
North Ridge	18.35
Harbor Point	22.8
Valley Base	9.49
Summit Lab	31.78

Note the Table.TransformColumnTypes step. It sets TempC to a real number first, so Number.Round has a number to work with.

Example 5: Round to the nearest hundred with negative digits

A negative digits value rounds to the left of the decimal point.

Number.Round(1268,-2)

Result: 1300

Passing -2 rounds to the nearest hundred. Use -1 for the nearest ten, -3 for the nearest thousand, and so on.

Things to keep in mind with Number.Round

The default is banker’s rounding, not round-half-up. Number.Round(2.5) returns 2, not 3, because RoundingMode.ToEven sends ties to the nearest even number. For classic round-half-up, pass RoundingMode.AwayFromZero.
The rounding mode only matters at an exact tie. If a value is not sitting precisely on a midpoint, every mode gives the same nearest result, so the mode is invisible unless your data hits exact halves.
Floating point can hide a tie. A value you think is an exact half, like 8.255, is often stored as 8.2549999... in binary, so the tie rule never fires. If banker’s rounding seems to do nothing, this is usually why.
The input has to be a real number. Text like "7.62" is not converted for you and the step errors. Set the column type with Table.TransformColumnTypes(..., type number) first, as in Example 4.
It rounds to the nearest value in both directions. If you always need to round up or always down regardless of ties, use Number.RoundUp or Number.RoundDown instead.

Common questions about Number.Round

How do I make Number.Round always round 0.5 up?

Pass RoundingMode.AwayFromZero as the third argument, for example Number.Round(2.5,0,RoundingMode.AwayFromZero). This is the closest match to Excel’s ROUND and to the everyday “round a half up” expectation.

What is the difference between Number.Round, Number.RoundUp, and Number.RoundDown?

Number.Round goes to the nearest value and uses the rounding mode to break ties. Number.RoundUp always rounds toward positive infinity, and Number.RoundDown always rounds toward negative infinity, neither of which cares about ties.

List of All Power Query Functions

Related Power Query Functions / Articles: