Value.Compare

One of the essential functions in the M language is the Value.Compare function. This function is used to compare the values of two arguments and return a result based on the comparison. In this article, we will take a closer look at the M code behind the Power Query M function Value.Compare.

What is Value.Compare?

The Value.Compare function is used to compare two values and return a result based on the comparison. The function takes two arguments, value1 and value2, and returns a result that indicates the relationship between the two values. The result can be one of the following:

– Less than (-1)

– Equal to (0)

– Greater than (1)

The syntax of the Value.Compare function is as follows:

Understanding the M Code Behind Value.Compare

The M code behind the Value.Compare function is straightforward and consists of a series of if-else statements. The code compares the two values passed as arguments and returns the appropriate result based on the comparison.

Here is the M code behind the Value.Compare function:

The code begins with the let statement, which is used to define a variable named Value.Compare. The variable is a function that takes two arguments, value1 and value2, and returns a number.

The first if statement in the function checks if value1 is less than value2. If this is true, the function returns -1, indicating that value1 is less than value2.

The second if statement checks if value1 is equal to value2. If this is true, the function returns 0, indicating that value1 is equal to value2.

The final else statement is executed if neither of the previous if statements is true. This means that value1 must be greater than value2, and the function returns 1, indicating that value1 is greater than value2.

Using the Value.Compare Function in Power Query

Now that we have a better understanding of the M code behind the Value.Compare function, let’s look at how we can use this function in Power Query.

Suppose we have a table of sales data that contains the sales figures for various products over several months. We want to compare the sales figures for each product between two specific months, say January and February, and highlight any products that have experienced a decrease in sales.

To do this, we can use the Value.Compare function to compare the sales figures for January and February for each product and determine if there has been a decrease in sales.

Here is the M code to achieve this:

The code first loads the sales data from the Excel workbook and converts the Month column to a date format.

The next step filters the table to include only the sales data for January and February. The table is then pivoted to show the sales figures for each product in separate columns for January and February.

The Value.Compare function is then used to compare the sales figures for January and February for each product, and the result is added as a new column named Sales Change.

Finally, the table is filtered to show only the products that have experienced a decrease in sales between January and February.

The Value.Compare function is a powerful tool in Power Query that allows users to compare the values of two arguments and return a result based on the comparison. The M code behind the function is straightforward and consists of a series of if-else statements. By understanding the M code behind the function, users can use it more effectively in their Power Query projects.

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

Upcoming Courses

Contact Us