Downloads
Power Query MNumberBeginner

Number.Abs

Returns the absolute value of a number, which is its size without its sign.

Syntax
Number.Abs(number as nullable number) as nullable number

Returns: 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

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

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.

Related functions

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