Number.Abs Function in Power Query M (4 Easy Examples)

If you want to strip the minus sign off a number and keep only its size, Number.Abs does exactly that. This article shows how it works on single values and on a whole column.

Table of Contents

Syntax of Number.Abs Function

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

where

number (required, nullable number). The value whose absolute value you want. Pass a positive number, a negative number, or null.

Returns: a nullable number, the absolute value (the distance from zero). If number is null, the result is null.

In plain terms, it drops the sign and gives you the magnitude.

Example 1: Use Number.Abs on a positive number

A positive number has no sign to remove, so it comes back unchanged.

let
    Source = Number.Abs(48)
in
    Source

Result: 48

Since 48 is already positive, Number.Abs leaves it alone.

Example 2: Use Number.Abs on a negative number

This is the common case. Feed it a negative value and the sign is removed.

let
    Source = Number.Abs(-127.5)
in
    Source

Result: 127.5

The minus sign is gone, but the decimal stays. Number.Abs does not round or truncate.

Example 3: Pass null to Number.Abs

Number.Abs is null-safe. A null input does not error.

let
    Source = Number.Abs(null)
in
    Source

Result: null

A null passes straight through as null. It is never turned into 0.

Example 4: Get absolute values for a whole column

Say you have budget variances where overspends are negative. You want the size of each gap regardless of direction.

Here is the starting data:

Department	Variance
Marketing	-2400
Logistics	1850
Support	-690
R&D	3120

let
    Source = Excel.CurrentWorkbook(){[Name="Variances"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"Variance",type number}}),
    Added = Table.AddColumn(Typed,"AbsVariance",each Number.Abs([Variance]),type number)
in
    Added

The result adds an AbsVariance column with the sign stripped from each row:

Department	Variance	AbsVariance
Marketing	-2400	2400
Logistics	1850	1850
Support	-690	690
R&D	3120	3120

Table.TransformColumnTypes sets the column to number first so the math runs. Then each Number.Abs([Variance]) applies the function row by row.

Things to keep in mind with Number.Abs

null flows through as null. A blank cell stays blank instead of becoming 0, so missing data can slip past unnoticed. Replace null values or filter them out if the next step needs a real number.
Coerce text columns first. Imported columns often arrive as text, which gives a type error. Run Table.TransformColumnTypes(Source,{{"Col",type number}}) before the function.
It only strips the sign. No rounding, no truncating, no type change. Number.Abs(-127.5) stays 127.5.
Zero stays zero. Both 0 and -0 come back as 0.

Common questions about Number.Abs

What’s the difference between Number.Abs and Number.Sign?

Number.Abs keeps the value and removes its sign, so -690 becomes 690. Number.Sign throws the value away and reports direction only, returning -1, 0, or 1.

List of All Power Query Functions

Related Power Query Functions / Articles: