Value.Equals

What is the Value.Equals Function?

Before diving into the M code behind Value.Equals, let’s first discuss what the function does. The Value.Equals function compares the values of two columns or variables in Power Query and returns a Boolean value indicating whether or not they are equal. The function takes two arguments: the first argument is the first column or variable to compare, and the second argument is the second column or variable to compare.

Here is an example of how the Value.Equals function can be used in a Power Query formula:

In this example, we are adding a new column to our source table called “Equal”. The value in this column will be determined by the result of the Value.Equals function, which compares the values in Column1 and Column2.

The M Code Behind Value.Equals

Now that we understand what the Value.Equals function does, let’s take a look at the M code behind it. The M code for Value.Equals looks like this:

Let’s break down what each line of this code does:

– `(Value1 as any, Value2 as any) as logical =>`: This line of code defines the function and sets the two arguments that the function takes (Value1 and Value2). The `as any` part means that the function can take any data type as an argument. The `as logical` part means that the function will return a Boolean value.

– `if Value1 is Value2 then true else false`: This line of code performs the actual comparison between Value1 and Value2. If they are equal, the function returns true. If they are not equal, the function returns false.

How Value.Equals Works

Now that we understand the M code behind Value.Equals, let’s talk about how the function actually works. When the function is called, it first checks to see if Value1 and Value2 are the same data type. If they are not, the function returns false. For example, if Value1 is a text string and Value2 is a number, the function will return false because they cannot be compared.

If Value1 and Value2 are the same data type, the function then checks to see if they are equal. If they are equal, the function returns true. If they are not equal, the function returns false.

Using Value.Equals in Power Query

Now that we understand how the Value.Equals function works, let’s talk about some best practices for using it in Power Query.

One important thing to keep in mind when using Value.Equals is that it is case sensitive. For example, if you are comparing two text strings, “Hello” and “hello”, the function will return false because they are not the same case. To avoid this issue, you can convert both strings to the same case using the Text.Lower function before using Value.Equals.

Another best practice is to include error handling in your formulas. If Value1 or Value2 contains null values, the Value.Equals function will return an error. To avoid this, you can use the try…otherwise statement to handle errors. Here’s an example:

In this example, if Value.Equals returns an error, the formula will return false instead.

In this article, we explored the M code behind the Power Query M function Value.Equals. We learned that the function compares the values of two columns or variables in Power Query and returns a Boolean value indicating whether or not they are equal. We also discussed how the function works and some best practices for using it in Power Query. Hopefully, this information will help you use Value.Equals more effectively in your Power Query formulas.

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

Upcoming Courses

Contact Us