The List.* M Functions And The equationCriteria Argument

Last week a reader left a comment on my blog asking what the third argument of the List.Contains() M function (somewhat cryptically called equationCriteria) does. I didn’t know, so I did some research and found out that lots of the List.* functions have the same argument. The documentation for List.Distinct() has a few examples but no real explanation of how they work. It also says:

…but there’s no link or indication where the documentation on ‘Parameter Values’ can be found. A bit more digging led me to the bottom of this page:

Equation criteria

Equation criteria for list values can be specified as either a

• A function value that is either
• A key selector that determines the value in the list to apply the equality criteria, or
• A comparer function that is used to specify the kind of comparison to apply. Built in comparer functions can be specified, see section for Comparer functions.
• A list value which has
• Exactly two items
• The first element is the key selector as specified above
• The second element is a comparer as specified above.

Still not exactly helpful, is it? After a bit of time testing, though, I think I’ve worked out how what’s possible with the equationCriteria argument and this blog post will, I hope, help any future M coders who are struggling with the same question. Let’s see some examples…

The basics

First of all, the basics. The following expression using List.Contains() returns TRUE because the text value “apples” appears in the list {“apples”, “oranges”, “pears”}:

```List.Contains({"apples", "oranges", "pears"}, "apples")
```

The following returns FALSE because the text value “grapes” does not appear in the list {“apples”, “oranges”, “pears”}:

```List.Contains({"apples", "oranges", "pears"}, "grapes")
```

However there are lots of different ways that text values can be compared and the equationCriteria argument allows you to specify which rules to follow.

Case sensitivity and culture

If you’ve written any M code you’ll know that it is case sensitive. As a result, the following returns FALSE:

```List.Contains({"apples", "oranges", "pears"}, "Apples")
```

What happens if you want to do a case-insensitive comparison though? This is where the Comparer functions come in. The Comparer.FromCulture() function returns a function that compares two values according to the rules of a given culture or locale and optionally ignore case, and can be used in the equationCriteria argument. The following example returns TRUE:

```List.Contains(
{"apples", "oranges", "pears"},
"Apples",
Comparer.FromCulture("en-GB", true)
)
```

In this case Comparer.FromCulture(“en-GB”, true) returns a function that compares two values for the English – Great Britain culture (for a full list of culture values, see the Language Tag column of the table on this page); the second, optional argument here makes the function ignore case when making the comparison. The function that Comparer.FromCulture() returns is then used by List.Contains() to make the comparison.

Rather than specify a culture you can also use the Culture.Current function to return the current system culture. For me, Culture.Current returns the value “en-GB” because I live in Great Britain and have my PC configured to use a British English locale:

The following example shows how Culture.Current can be used with Comparer.FromCulture and also returns TRUE, at least for me:

```List.Contains(
{"apples", "oranges", "pears"},
"Apples",
Comparer.FromCulture(
Culture.Current,
true
)
)
```

If you’re curious to see an example where different cultures produce different results here’s one I stole from this article on string comparisons and sorting in .NET. Apparently in English the character æ is treated the same as the combination of the two characters ae but this is not the case in Danish. As a result the following returns TRUE:

```List.Contains(
{"aepples", "oranges", "pears"},
"æpples",
Comparer.FromCulture(
"en-GB",
true
)
)
```

Whereas this returns FALSE:

```List.Contains(
{"aepples", "oranges", "pears"},
"æpples",
Comparer.FromCulture(
"da-DK",
true
)
)
```

Ordinal comparisons

If you don’t want all the uncertainty of cultures and case sensitivity you can just make an ordinal comparison, which will compare two strings by finding the unicode character value for each character in each string and compare those values. To do this you can use the Comparer.Ordinal() function. The following returns FALSE:

```List.Contains(
{"apples", "oranges", "pears"},
"Apples",
Comparer.Ordinal
)
```

…because “a” is not the same unicode character as “A”, and so “apples” and “Apples” are not treated as equal.

Custom comparer functions

As the documentation hints you can also write your own function to do the comparison. A comparer function is just – as far as I can see – a function that has two arguments and returns a logical value. Here’s an example of a custom function that takes two text values, x and y, and returns true if the first three characters of x are the same as y:

```(x as text, y as text)=>Text.Start(x,3)=y
```

It can be used with List.Contains() as in the following example, which returns TRUE:

```List.Contains(
{"apples", "oranges", "pears"},
"app",
(x as text, y as text)=>Text.Start(x,3)=y
)
```

What must be happening here is that the function is called three times, every value in the list {“apples”, “oranges”,”pears”} is being passed to the x argument and for each call “app” is passed to y; because the first three characters of “apples” are “app” the function returns true in this case, so List.Contains() returns true.

Key selectors

If you’re working with a list of records you might only want to do the comparison on one field in the record, and this is what key selectors allow you to do. The following example, which returns TRUE:

```List.Contains(
{[Fruit="apples", Colour="Red"],
[Fruit="oranges", Colour="Orange"],
[Fruit="pears", Colour="Green"]},
[Fruit="apples", Colour="Russet"],
each [Fruit]
)
```

…does so because it only compares the Fruit field in each record, and the Fruit fields in [Fruit=”apples”, Colour=”Red”] and [Fruit=”apples”, Colour=”Russet”] are indeed the same. However the following example returns FALSE:

```List.Contains(
{[Fruit="apples", Colour="Red"],
[Fruit="oranges", Colour="Orange"],
[Fruit="pears", Colour="Green"]},
[Fruit="apples", Colour="Russet"],
each [Colour]
)
```

…because the Colour “Russet” does not appear anywhere in the Colour field of any of the records in the first parameter.

Combining key selectors and comparison functions

Finally, as the documentation suggests, you can combine the above methods of comparison by passing a list containing two items to equationCriteria: the first item in the list must be a key selector, the second must be a comparer function. For example, the following returns TRUE:

```List.Contains(
{[Fruit="apples", Colour="Red"],
[Fruit="oranges", Colour="Orange"],
[Fruit="pears", Colour="Green"]},
[Fruit="Apples", Colour="Russet"],
{each [Fruit], Comparer.FromCulture("en-GB", true)}
)
```

…because it only looks at the Fruit field of each record, and it does a case-insensitive comparison using the en-GB culture, so “apples” and “Apples” are equal.

21 responses

1. The Power Query team has been doing a great technical job. But, maybe they need to hire a few people to write “good” help files for the M language.

2. Daniel says:

Excellent post as usual!
I’m just wondering to know if there is a way to compare strings using wildcards to answer questions like : Does the text contains “pple” ? (regardless of the the position and number of characters to the left or right)
Using sql syntex this can be achieved by using: like %pple%

I’ve never found something similar in power query M language….

Thanks, Daniel

3. @ Daniel,
you can do this by adjusting the example from the custom comparer functions like this:
= List.Contains(
{“apples”, “oranges”, “pears”},
“pple”,
(x as text, y as text)=>Text.Contains(x,y)

@ Chris: Thx so much for this – top helpful & relevant !!

• Daniel says:

Hi @Imke. Thank you!
A more complex scenario would be to check if the string has “pple” and replace it by another text like “;” for instance.
Is there easy way to do this as well?

Many thanks for your time and help!
Daniel

• Hi Daniel, do you mean this?:
= List.Transform( {“apples”, “oranges”, “pears”}, each Text.Replace(_, “pple”,”;”))

or shall the list also be filtered down to those elements who contain that string?

• Daniel says:

Hi @Imke. Thank you! You are great!
Thank you so much for your help!

• Daniel says:

Sorry @Imke! last question haha (as I realized you are very good with this 🙂

What if I wanted to use wildcards to replace “pp??” by another text. Where “??” means any combination of 2 characters… So it would consider text like “pple” or “pptd” and so on…

• Stefano Morpurgo says:

It is kind of old discussion, but in case others still look for a solution to match a string with wildcards: simply remove the characters in the positions of the wildcards. For example, to match F?PJ, do the following:

Text.Contains(_,”PJ”) and Text.Contains(Text.RemoveRange(Text.PositionOf(_,”PJ”)-1),”FPJ”)

This checks if the text contains PJ, if so, removes the character before PJ and checks if this is now FPJ. Can be used with fixed number of wildcards only (? only, not *)

4. Thanks, Chris! Excellent, as always!
Used first half of this compares, but key selector and combinations is a new great stuff for me.
Maxim

5. Colin Banfield says:

Hi Chris,

Very good article on the topic. The only notable omission is the lack of mention of the Comparer.OrdinalIgnoreCase enum, which I use frequently.

6. Great.
and a Question.
What is “Comparer.Ordinal” in Power BI Service ?

• Comparer.Ordinal is an M function, and it returns a function that takes two values and compares those two values according to ordinal rules.

7. Hi Chris, Nice article !
I am currently using this: List.Intersect({ {“A”,”A”,”B”} , {“A”} } ) and I am wondering how I can use the optional equationCriteria to retrieve a list including the matching items WITH duplicates.
So in that case, I want it to return: {“A”,”A”} and not just {“A”}.

• You can get the UI to generate the code to do this for you: select your column and then go to the Home tab and click Keep Rows/Keep Duplicates.

8. EdH says:

So, any ideas why the following happens:
= Comparer.Ordinal(“1234567-114″,”1234567”) = 1
= Comparer.Ordinal(“1234567-114″,”1234567-200”) = -1

Neither are a match by the result isn’t the same.

• I’m not exactly sure why you get 1 and -1 here, but the way I would expect to use Comparer.Ordinal is like this:
Comparer.Equals(Comparer.Ordinal, “1234567-114″,”1234567”)
Comparer.Equals(Comparer.Ordinal, “1234567-114″,”1234567-200”)
…both of which return FALSE

• edh says:

Thanks Chris. I’ll give that a shot.(never got a reply email for some reason is why I am posting so late.)

9. Stephen Wright says:

Thanks for this. Very useful, as always – even 2 years later!

You can also do partial matches by changing it to “, (x as text, y as text)=>Text.Contains(y,x)))”

To combine partial and case insensitive matches I had to change it to “=>Text.Contains(Text.Lower(y),Text.Lower(x))”. I can’t see a way to use both Comparer.OrdinalIgnoreCase and a custom function.

10. Martin East says:

Hi Chris,

when using Text.Contains with my own custom comparer function as the third argument, I get the error “The specified comparer does not support culture sensitive comparisons”. Do you have any idea how I can make my custom comparer support culture sensitive comparisons? I can’t find anything in the documentation to explain this.

(If you are wondering why I need a custom comparer function, it’s for matching single-character wildcards, e.g. # for digit, @ for letter etc. – so A123X would equal A###@ . I tried to write my own TextContains function, but it was too slow, as I had to split the texts into lists of characters before comparing each character pair)

Best
Martin

This site uses Akismet to reduce spam. Learn how your comment data is processed.