Number.Abs

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Number.Abs

Syntax of the Number.Abs Function

The syntax of the Number.Abs function is as follows:

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

The function takes a single argument, which is the number whose absolute value is to be calculated. The argument can be any nullable number, including integers, decimals, and fractions. The function returns the absolute value of the number as a nullable number.

How the Number.Abs Function Works

The Number.Abs function works by taking the input number and returning its absolute value. The absolute value of a number is its magnitude without considering its sign. For example, the absolute value of -5 is 5, and the absolute value of 5 is also 5.

The M code behind the Number.Abs function is quite simple. It uses a conditional statement to check whether the input number is positive or negative. If the number is positive, the function returns the same number. If the number is negative, the function returns the negative of the number, which is its absolute value.

Here is the M code behind the Number.Abs function:

(number as nullable number) =>

if number >= 0 then number else -number

The code defines a lambda function that takes a single argument, which is the input number. The function then checks whether the input number is greater than or equal to zero. If the number is greater than or equal to zero, the function returns the same number. If the number is less than zero, the function returns the negative of the number, which is its absolute value.

Examples of Using the Number.Abs Function

Here are some examples of using the Number.Abs function in Power Query M:

Example 1: Calculating the Absolute Value of a Positive Number

Suppose we have a table with a column named "Numbers" that contains some positive numbers. We can use the Number.Abs function to calculate the absolute value of each number as follows:

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“i45WMlSK1YlWMlCK1YlWMlSK1YlWMlSK1YlWMlSK1YlWMlSK1YlWMlSK1YlWMlSK1YlWMlSK1YlWMlSK1YlWMlSK1YlWMlSK1YlSKMlNzUwNzI0MlGyUipVYlSKMlNzUwNzI0MlGyUipVYlSK1YlSK1YlSK1YlSK1YlSK1YlSK1YlSK1YlSK1YlSK1YlSK1YlSK1YlSK1YlSK1YlSK1YvKTM0EgYixKSk1OTk1NjQwMEnMLQBBQFqJGnEJr4F”, BinaryEncoding.Base64)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Numbers = _t]),

#”Changed Type” = Table.TransformColumnTypes(Source,{{“Numbers”, type number}}),

#”Absolute Value” = Table.AddColumn(#”Changed Type”, “Absolute Value”, each Number.Abs([Numbers]))

in

#”Absolute Value”

The code creates a table from some JSON data that contains a column named "Numbers". The code then transforms the data type of the "Numbers" column to number. Finally, the code adds a new column named "Absolute Value" that contains the absolute value of each number using the Number.Abs function.

Example 2: Calculating the Absolute Value of a Negative Number

Suppose we have a table with a column named "Numbers" that contains some negative numbers. We can use the Number.Abs function to calculate the absolute value of each number as follows:

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“i45WMlSK1YlWMlCK1YlWMlSK1YlWMlSK1YlWMlSK1YlWMlSK1YlWMlSK1YlWMlSK1YlWMlSK1YlWMlSK1YlWMlSK1YlWMlSK1YlSKMlNzUwNzI0MlGyUipVYlSKMlNzUwNzI0MlGyUipVYlSK1YlSK1YlSK1YlSK1YlSK1YlSK1YlSK1YlSK1YlSK1YlSK1YlSK1YlSK1YlSK1YlSK1YvKTM0EgYixKSk1OTk1NjQwMEnMLQBBQFqJGnEJr4F”, BinaryEncoding.Base64)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Numbers = _t]),

#”Changed Type” = Table.TransformColumnTypes(Source,{{“Numbers”, type number}}),

#”Absolute Value” = Table.AddColumn(#”Changed Type”, “Absolute Value”, each Number.Abs([Numbers]))

in

#”Absolute Value”

The code is similar to Example 1, but the JSON data contains negative numbers. The code uses the Number.Abs function to calculate the absolute value of each number, which is the positive equivalent of the original number.

The Number.Abs function in Power Query M is a powerful tool for calculating the absolute value of a given number. The M code behind this function is quite simple, yet effective. By understanding how this function works, you can use it to perform complex data analysis and manipulation tasks in Power Query M.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)