Number.Sqrt

D

T

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

Understanding the Number.Sqrt Function

The Number.Sqrt function is a mathematical function that is used to calculate the square root of a given number. It is a built-in function in Power Query M and is used in various data transformation tasks. The syntax for the Number.Sqrt function is as follows:


Number.Sqrt(number as any) as nullable any


The function takes a single argument, which is the number whose square root needs to be calculated. The argument can be of any data type, including integers, decimals, and fractions. The function returns the square root of the number as a nullable any data type.

Examples of Using the Number.Sqrt Function

Let's take a look at some examples of using the Number.Sqrt function in Power Query M.

Example 1: Calculating the Square Root of a Number

Suppose we have a table that contains a column named "Value" and we want to calculate the square root of each value in that column. We can use the Number.Sqrt function to achieve this as shown below:


let

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

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

#”Added Custom” = Table.AddColumn(#”Changed Type”, “Sqrt”, each Number.Sqrt([Value])),

#”Removed Columns” = Table.RemoveColumns(#”Added Custom”,{“Value”})

in

#”Removed Columns”


In the above code, we first converted the data source to a table and changed the data type of the "Value" column to number. We then added a custom column named "Sqrt" that uses the Number.Sqrt function to calculate the square root of each value in the "Value" column. Finally, we removed the "Value" column from the table and returned the resulting table.

Example 2: Using the Number.Sqrt Function in a Conditional Statement

Suppose we have a column named "Value" in a table and we want to create a new column that contains the square root of the value only if it is greater than 10. We can use the Number.Sqrt function within a conditional statement to achieve this as shown below:


let

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

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

#”Added Custom” = Table.AddColumn(#”Changed Type”, “Sqrt If > 10”, each if [Value] > 10 then Number.Sqrt([Value]) else null),

#”Removed Columns” = Table.RemoveColumns(#”Added Custom”,{“Value”})

in

#”Removed Columns”


In the above code, we first converted the data source to a table and changed the data type of the "Value" column to number. We then added a custom column named "Sqrt If > 10" that uses the Number.Sqrt function within a conditional statement to calculate the square root of the value only if it is greater than 10. Finally, we removed the "Value" column from the table and returned the resulting table.

In conclusion, the Number.Sqrt function is a powerful mathematical function in Power Query M that is used to calculate the square root of a given number. It can be used in various data transformation tasks, including conditional statements and custom column creation. Understanding the M code behind this function is essential for effective data transformation and manipulation 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)