Power Query: More Searching Questions

Welcome to our Power Query blog. This week, I answer some (even) more searching questions.

I have some tent data and some search strings, as before…

Last time, I extracted my data to Power Query and ran a function to check for the list values in the tent data.

The M code I used was:

The results obtained were:

The word ‘bar’ does appear in the ‘Party’ package, but it’s not the right case. I will look how to tweak my query to do this. I will also look at how to make exact matches, which means that “awning” and “awnings” would not be recognised as a match.

As I hinted last week, the first solution lies with the third parameter of Text.Contains().

Text.Contains(text as nullable text, substring as text, optional comparer as nullable function) as nullable logical

A list of comparer functions is available at: https://docs.microsoft.com/en-us/powerquery-m/comparer-functions

The comparer I am going to use here is Comparer.OrdinalIgnoreCase.

When I enter this change, I can see that ‘bar’ and ‘BAR’ now match:

Finally, I want to make a distinction between “awning” and “awnings”, so that ‘Wedding’ package would no longer match the search string ‘awning’ on my list.

I will use a different list function, namely List.ContainsAny().

List.ContainsAny(list as list, values as list, optional equationCriteria as any) as logical

Since I am comparing two lists, I need to change the rest of my function.

When I enter this, I can see that ‘Wedding’ no longer matches:

The M code I have used is:

Since I am comparing two lists, I have to split the Included column into separate words and compared that list of words with my SearchStrings list.  Note that this no longer ignores case, and so ‘Party’ package no longer matches either.  I can fix this by using the optional third parameter in List.ContainsAny:

List.ContainsAny(list as list, values as list, optional equationCriteria as any) as logical

The equationCriteria in this case will use a comparer.

Note that before this would work, I needed to remove the commas and full stops using ‘Replace Values’ on the Transform tab, since the exact match was rejecting words followed by a comma or full stop.

The M code I have used is:

This adds a condition to ignore case differences between x (the split text list) and y (the SearchStrings list). The case is now ignored for the ‘Party’ package.

Come back next time for more ways to use Power Query!