Number.RoundUp
Rounds a number up to the next value at a set number of digits.
Number.RoundUp(number as nullable number, optional digits as number) as nullable numberReturns: The rounded number, or null when the input is null.
What it does
Number.RoundUp always moves a value up to the next figure at the digit you choose. It does not look at the half-way point the way Number.Round does. Use it when the business rule is to round up no matter what, such as charging the next whole unit for a freight quote or a billable block of time.
Syntax and arguments
- number: the value to round up.
- digits (optional): the number of decimal places to keep. The default is 0, which rounds up to a whole number.
Example
A logistics vendor bills freight in whole dollars, rounded up. The accounts payable team estimates the cost of each shipment by rounding up the calculated freight.
let
Quotes = #table(type table [Shipment = text, Freight = number],
{{"SHP-101", 12.1}, {"SHP-102", 12.9}, {"SHP-103", 40}}),
Billed = Table.AddColumn(Quotes, "BilledFreight", each Number.RoundUp([Freight]), type number)
in
Billed
For the positive values here, 12.1 and 12.9 both move up to 13, and 40 stays at 40 because it is already a whole number. Number.Round would have given 12 for 12.1 and 13 for 12.9, which is a different answer.
Example: a second, different use
Round an estimated cost of a project up to the nearest hundred dollars with a negative digits value, so a budget always has a little headroom.
Things to watch
- It rounds up in the direction of a larger value. For negative numbers, test the result on a sample row before you rely on it, because the direction for negatives is easy to misread.
- It is not the same as the accounting rule for halves. Use Number.Round with RoundingMode.AwayFromZero for that.
- Rounding up every line can inflate a total, so think about where the rule applies.
- Null in gives null out.
In business terms
Freight estimates, cloud usage charges and time billed in blocks are often rounded up by contract. Using the same rule in the query as in the contract keeps the forecast and the invoice in agreement.