Number.Abs
Returns the absolute value of a number, which is its size without its sign.
Number.Abs(number as nullable number) as nullable numberReturns: The positive value of the number, or null when the input is null.
What it does
Number.Abs gives the size of a number and drops its sign. A variance of -350 and a variance of 350 are the same size, so Number.Abs is used when a report must rank or threshold differences regardless of direction. A review rule such as "flag any variance over 500 either way" depends on it.
Syntax and arguments
- number: the value to take the absolute value of.
Example
A variance report flags any budget line that is off by more than a set amount, whether over or under budget. The threshold test uses the absolute value.
let
Variances = #table(type table [Line = text, Variance = number],
{{"Rent", -850}, {"Travel", 120}, {"Software", 1500}, {"Insurance", -40}}),
Flagged = Table.AddColumn(Variances, "Flag",
each if Number.Abs([Variance]) > 500 then "Review" else "OK", type text)
in
Flagged
Rent and Software are flagged for review, because their variances are larger than 500 in size, even though Rent is under budget. Travel and Insurance are within the threshold.
Example: a second, different use
Show the size of a difference between two bank balances as a positive amount on a reconciliation sheet, with the direction stated in a separate column.
Things to watch
- Absolute value hides the direction. Keep the signed value in a column as well, so a reviewer can tell over from under.
- It does not round. Use Number.Round for that.
- For a difference between two dates, use Duration.Days and then Number.Abs if the order of the dates is not known.
- Null in gives null out.
In business terms
Variance analysis and reconciliation both need to know how large a difference is, and often whether it is over or under. Using the absolute value for the threshold and the signed value for the explanation keeps both views honest.