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:
For more information about equationCriteria, see Parameter Values.
…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”}:
[sourcecode language=”text” padlinenumbers=”true”]
List.Contains({"apples", "oranges", "pears"}, "apples")
[/sourcecode]
The following returns FALSE because the text value “grapes” does not appear in the list {“apples”, “oranges”, “pears”}:
[sourcecode language=”text”]
List.Contains({"apples", "oranges", "pears"}, "grapes")
[/sourcecode]
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:
[sourcecode language=”text”]
List.Contains({"apples", "oranges", "pears"}, "Apples")
[/sourcecode]
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:
[sourcecode language=”text”]
List.Contains(
{"apples", "oranges", "pears"},
"Apples",
Comparer.FromCulture("en-GB", true)
)
[/sourcecode]
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:
[sourcecode language=”text”]
List.Contains(
{"apples", "oranges", "pears"},
"Apples",
Comparer.FromCulture(
Culture.Current,
true
)
)
[/sourcecode]
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:
[sourcecode language=”text”]
List.Contains(
{"aepples", "oranges", "pears"},
"æpples",
Comparer.FromCulture(
"en-GB",
true
)
)
[/sourcecode]
Whereas this returns FALSE:
[sourcecode language=”text”]
List.Contains(
{"aepples", "oranges", "pears"},
"æpples",
Comparer.FromCulture(
"da-DK",
true
)
)
[/sourcecode]
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:
[sourcecode language=”text”]
List.Contains(
{"apples", "oranges", "pears"},
"Apples",
Comparer.Ordinal
)
[/sourcecode]
…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:
[sourcecode language=”text”]
(x as text, y as text)=>Text.Start(x,3)=y
[/sourcecode]
It can be used with List.Contains() as in the following example, which returns TRUE:
[sourcecode language=”text”]
List.Contains(
{"apples", "oranges", "pears"},
"app",
(x as text, y as text)=>Text.Start(x,3)=y
)
[/sourcecode]
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:
[sourcecode language=”text”]
List.Contains(
{[Fruit="apples", Colour="Red"],
[Fruit="oranges", Colour="Orange"],
[Fruit="pears", Colour="Green"]},
[Fruit="apples", Colour="Russet"],
each [Fruit]
)
[/sourcecode]
…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:
[sourcecode language=”text”]
List.Contains(
{[Fruit="apples", Colour="Red"],
[Fruit="oranges", Colour="Orange"],
[Fruit="pears", Colour="Green"]},
[Fruit="apples", Colour="Russet"],
each [Colour]
)
[/sourcecode]
…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:
[sourcecode language=”text”]
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)}
)
[/sourcecode]
…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.
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.
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
@ 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 !!
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?
Hi @Imke. Thank you! You are great!
Thank you so much for your help!
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…
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 *)
Thanks, Chris! Excellent, as always!
Used first half of this compares, but key selector and combinations is a new great stuff for me.
Maxim
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.
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.
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.
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
Thanks Chris. I’ll give that a shot.(never got a reply email for some reason is why I am posting so late.)
Because ”1234567-200” > “1234567-114″ > ”1234567”
“A comparer function accepts two arguments and returns -1, 0, or 1 based on whether the first value is less than, equal to, or greater than the second.”
https://learn.microsoft.com/en-us/powerquery-m/comparer-ordinal
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.
With:
(x as text, y as text)=>Text.Contains(x, y, Comparer.OrdinalIgnoreCase))
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
I don’t know, sorry. Have you asked on the Power Query https://social.technet.microsoft.com/Forums/en-US/home?forum=powerquery ?
I am facing the same problem, Martin, have you found a solution yet? Could not find anyting on the forum Chriss suggested.
No, I haven’t found anything, even though I look for something every so often – hence my stumbling onto this old post. The PQ team seems to be allergic to anything that even remotely looks like regular expressions. Unfortunately if you build your own functions they will be impractically slow for all but the smallest datasets