Number.RoundUp Function in Power Query M (5 Easy Examples)

If you want to push a number up to the next whole value or the next decimal step, Number.RoundUp is the function to use. It always rounds toward positive infinity, so positive values move up and the result never lands below the original at the chosen precision.

Table of Contents

Syntax of Number.RoundUp Function

Number.RoundUp(number as nullable number, optional digits as nullable number) as nullable number

where

number (required, nullable number). The value you want to round up toward positive infinity.
digits (optional, nullable number). The number of decimal places to round to. It defaults to 0, which rounds up to the next whole integer.

Returns: A nullable number rounded up to the precision set by digits. If number is null, the function returns null.

In plain terms, Number.RoundUp looks at the decimal place you point it at and bumps the number up to the next step. For positive numbers, it always goes higher.

Example 1: Round up to the next whole number

Here digits is left out, so Number.RoundUp rounds up to the next integer.

Number.RoundUp(3.12)

Result: 4

Even though 3.12 is much closer to 3, Number.RoundUp ignores how close it is and pushes the value up to 4.

Example 2: Round up to a set number of decimals

Pass 2 as the second argument to round up at the second decimal place.

Number.RoundUp(12.0034,2)

Result: 12.01

The digits past the second decimal place force the value up, so 12.0034 becomes 12.01 rather than 12.00.

Example 3: Rounding up a negative number

This is the one that trips people up. You might expect -2.1 to become -3, but it does not.

Number.RoundUp(-2.1)

Result: -2

Number.RoundUp rounds toward positive infinity. For a negative number, moving toward positive infinity means moving closer to zero, so -2.1 becomes -2.

Example 4: Round shipping weights up to whole kilograms

Say you bill parcels by the whole kilogram and need to round each weight up. Here is the source table.

Parcel	WeightKg
A101	2.1
A102	5.7
A103	0.4
A104	10

This query adds a BilledKg column that rounds each weight up to the next whole kilogram.

let
    Source = Excel.CurrentWorkbook(){[Name="Shipments"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"WeightKg",type number}}),
    Added = Table.AddColumn(Typed,"BilledKg",each Number.RoundUp([WeightKg]),Int64.Type)
in
    Added

Parcel	WeightKg	BilledKg
A101	2.1	3
A102	5.7	6
A103	0.4	1
A104	10	10

Any weight with a fractional part is pushed up to the next whole number. 10 is already a whole number, so it stays at 10.

Example 5: Round invoice amounts up to two decimals

For billing you often round charges up to the cent. Here is the source table.

Invoice	RawAmount
INV-01	12.341
INV-02	8.202
INV-03	5.005
INV-04	19.99

This query adds a ChargeAmount column that rounds each amount up to two decimal places.

let
    Source = Excel.CurrentWorkbook(){[Name="Invoices"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"RawAmount",type number}}),
    Added = Table.AddColumn(Typed,"ChargeAmount",each Number.RoundUp([RawAmount],2),type number)
in
    Added

Invoice	RawAmount	ChargeAmount
INV-01	12.341	12.35
INV-02	8.202	8.21
INV-03	5.005	5.01
INV-04	19.99	19.99

Each amount with extra digits past the second decimal is rounded up to the next cent. 19.99 is already exact at two decimals, so it is left unchanged.

Things to keep in mind with Number.RoundUp

Number.RoundUp rounds toward positive infinity, like a ceiling. For positive numbers it always bumps up to the next step, so Number.RoundUp(3.12) is 4.
Negative numbers go toward zero, not further away. Number.RoundUp(-2.1) is -2, even though many people expect -3. This is the most common surprise.
It is the mirror of Number.RoundDown, which floors toward negative infinity. For negative values the two functions move in opposite directions.
Unlike Number.Round, which picks the nearest value, Number.RoundUp ignores how close the fraction is and always rounds up toward positive infinity.
digits can be negative to round up to tens, hundreds, and beyond. A null input returns null, and text pulled in from an import should be coerced to a number first.

Common questions about Number.RoundUp

What’s the difference between Number.RoundUp and Number.Round?

Number.Round rounds to the nearest value at the chosen precision. Number.RoundUp always rounds toward positive infinity, no matter how close the fraction is, so Number.RoundUp(1.234,2) gives 1.24 while Number.Round(1.234,2) gives 1.23.

Does Number.RoundUp round negatives away from zero?

No. It rounds toward positive infinity, which for a negative number means moving closer to zero. That is why Number.RoundUp(-2.1) returns -2 rather than -3.

List of All Power Query Functions

Related Power Query Functions / Articles: