Value.Divide

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Value.Divide

The Value.Divide function is used to divide the values in two columns. It takes two arguments: the numerator and the denominator. For example, if we have a table with two columns – Sales and Cost of Goods Sold – we could use the Value.Divide function to calculate the gross profit margin.

But have you ever wondered what the M code behind the Value.Divide function looks like? In this article, we will take a deep dive into the M code that powers the Value.Divide function.

The Syntax of the Value.Divide Function

Before we dive into the M code, let’s first take a look at the syntax of the Value.Divide function.

Value.Divide(numerator as any, denominator as any, optional precision as nullable number) as nullable number

The Value.Divide function takes three arguments: the numerator, the denominator, and an optional precision. The numerator and denominator can be of any type, while the precision must be a nullable number. The function returns a nullable number.

The M Code Behind the Value.Divide Function

Now that we know the syntax of the function, let’s take a look at the M code that powers it.

let

divide = (numerator, denominator, optional precision) =>

if denominator = 0 or numerator = null or denominator = null then null

else if precision = null then numerator / denominator

else Number.Round(numerator / denominator, precision),

result = Table.AddColumn(Source, NewColumnName, each divide([Numerator], [Denominator], null))

in

result

The Value.Divide function is actually a custom function created using the let…in syntax. This function takes three arguments: numerator, denominator, and an optional precision.

The first line of the function checks to see if the denominator is zero or if either the numerator or denominator is null. If either of these conditions is true, then the function returns null.

If neither of these conditions is true, then the function moves on to the next line, which checks to see if the precision argument is null. If it is null, then the function simply divides the numerator by the denominator.

If the precision argument is not null, then the function uses the Number.Round function to round the result of the division to the specified number of decimal places.

Finally, the function adds a new column to the table that contains the result of the custom function. The new column is named according to the value passed as NewColumnName.

The Value.Divide function is a powerful tool in the Power Query arsenal. By understanding the M code that powers the function, you can gain a deeper understanding of how Power Query works and how you can use it to manipulate your data.

In this article, we took a deep dive into the M code behind the Value.Divide function. We learned how the function works, what arguments it takes, and how it is used to create a new column in a Power Query table.

Hopefully, this article has given you a better understanding of the Value.Divide function and how you can use it in your own data analysis projects.

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)