Downloads
Power Query MNumberBeginner

Number.Sign

Returns -1 for a negative number, 1 for a positive number and 0 for zero.

Syntax
Number.Sign(number as number) as number

Returns: -1, 0 or 1.

What it does

Number.Sign reduces a number to its direction: -1 for negative, 0 for zero and 1 for positive. Finance teams use it to label a movement as an increase or a decrease, or to split a ledger into debits and credits in a report, without writing a long if statement.

Syntax and arguments

Example

A cash flow report labels each movement as an inflow, an outflow or no movement, so the reader can see the direction of each line.

let
    Movements = #table(type table [Item = text, Amount = number],
        {{"Customer receipts", 45000}, {"Payroll", -18000}, {"Bank fees", 0}}),
    Labeled = Table.AddColumn(Movements, "Direction", each
        if Number.Sign([Amount]) = 1 then "Inflow"
        else if Number.Sign([Amount]) = -1 then "Outflow"
        else "None", type text)
in
    Labeled

Customer receipts is an inflow, Payroll is an outflow and Bank fees is labeled None, because its amount is zero. The amounts are not changed.

Example: a second, different use

Multiply by the sign of a balance to convert a signed amount into a normal balance that is shown as a positive number for the reader.

Things to watch

In business terms

Cash flow statements, bank activity summaries and debit and credit reports all depend on the direction of each amount. Labeling direction in the query keeps the presentation honest about money coming in and going out.

Related functions

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