Number.Sign
NumberReturns a number indicating the sign of a value: 1 for positive, -1 for negative, 0 for zero.
Syntax
Number.Sign(number as nullable number) as nullable numberParameters
| Name | Type | Required | Description |
|---|---|---|---|
number | number | Yes | The number whose sign is evaluated. |
Return Value
number — 1 if the number is positive, -1 if negative, 0 if zero, null if null.
Remarks
Number.Sign returns a number indicating the sign of its input: 1 for any positive value, -1 for any negative value, and 0 for zero. If the input is null, it returns null.
This function is a clean alternative to branching if expressions when you need to classify values by direction or apply sign-based arithmetic. Common uses include:
- Directional labeling: classifying rows as gain/loss/flat based on a change value
- Sign normalization: multiplying by Number.Sign(x) to obtain the unit direction without the magnitude
- Conditional logic: using the result in arithmetic directly (value * Number.Sign(threshold - value)) instead of branching
Note that Number.Sign returns an integer -1, 0, or 1 — not a text label. If you need text labels, use an if expression based on the sign result. Also note that Number.Sign(NaN) returns NaN, not 0, so guard for NaN if your data may contain it.
Examples
Example 4: Classify orders as Gain, Loss, or Flat based on quantity change
Table.AddColumn(
Table.SelectColumns(Table.FirstN(Sales, 5), {"OrderID", "CustomerName", "Quantity"}),
"Direction",
each
if Number.Sign([Quantity] - 4) = 1 then "Above Average"
else if Number.Sign([Quantity] - 4) = -1 then "Below Average"
else "Average",
type text
)OrderID | CustomerName | Quantity | Direction | |
|---|---|---|---|---|
| 1 | 1 | Alice | 4 | Average |
| 2 | 2 | Bob | 2 | Below Average |
| 3 | 3 | Charlie | 10 | Above Average |
| 4 | 4 | Alice | 1 | Below Average |
| 5 | 5 | Diana | 6 | Above Average |