Comparer.Equals is a function in the Power Query M language that checks the equality of two given values using a provided comparer. The function returns a logical value based on the equality comparison.
Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365
Comparer.Equals( comparer as function, x as any, y as any, ) as logical
|Comparer||Comparer Functions control how to compare values. Comparer.Ordinal tests values for equality in a case-sensitive way. To ignore capitalization when you can provide Comparer.OrdinalIgnoreCase, whereas Comparer.FromCulture can be used for a culture-aware comparison.|
|X||The first value in the comparison.|
|Y||The value to compare with.|
Comparer.Equals function offers a way to determine if two given values are identical or not. Based on the check it return true or false. The function supports the use a custom comparer to guide this comparison. Why is this so crucial? Well, comparisons aren’t always as straightforward as they seem. Sometimes, case-sensitivity matters, while at other times, cultural nuances are important.
So, what exactly is a comparer? Think of it as a set of rules or guidelines that determine how two values should be compared. The Power Query M language provides some built-in comparers.
The following built-in comparers are available in the formula language:
- Comparer.Ordinal: Used to perform an exact ordinal comparison where every character must match exactly.
- Comparer.OrdinalIgnoreCase: Used to perform an ordinal comparison in a case-insensitive manner.
- Comparer.FromCulture: respects cultural variance in text instructed with a culture code.
Let’s look at a few examples to see the Comparer.Equals function in action.
Naturally, by using the comparer Comparer.Ordinal, testing whether ‘a’ is equal to ‘a’ results in true.
Comparer.Equals( Comparer.Ordinal, "a", "a" ) // Output: true
Now let’s say you want to see if two letters are exactly the same, with their case being a distinguishing factor. Using Comparer.Ordinal to compare the lowercase “a” and the uppercase “A” would confirm they aren’t the same.
Comparer.Equals( Comparer.Ordinal, "a", "A" ) // Output: false
However, there are many other ways in which you can compare values, one of them being case-insensitive. The comparer argument allows you to specify the rules for the comparison.
So what can you do when the case doesn’t matter to you? Say, you just want to know if they’re the same character, regardless of whether they’re uppercase or lowercase. You would use the comparer Comparer.OrdinalIgnoreCase.
Comparer.Equals( Comparer.OrdinalIgnoreCase, "a", "A" ) // Output: true
Culture aware comparison
Another fun comparer to use with Comparer.Equals is Comparer.FromCulture. It performs a comparison in line with the rules of a given culture or locale and optionally lets you ignore case. Let’s look at a few examples.
Between languages, special characters can be considered differently. For instance, the character ‘æ’in English is equal to ‘ea’.
Comparer.Equals( Comparer.FromCulture( "en-US" ), "Færdig", "Faerdig" ) // Output: true
However, languages like Danish beg to differ. For them, the same character holds its own unique value. In Danish, the word for ‘Finished’ is ‘Faerdig’, and it doesn’t equate ‘æ’ with ‘ea’.
Comparer.Equals( Comparer.FromCulture( "da-DK" ), "Færdig", "Faerdig" ) // Output: false
In conclusion, the Comparer.Equals function offers a nuanced approach to equality checks, accommodating ordinal, capital-sensitive and culturally sensitive comparisons. As you dive deeper into Power Query’s M language, understanding these comparers will undoubtedly prove useful.
While the Comparer.Equals function can be used on its own, it is also used by the following functions:
Other functions related to Comparer.Equals are: