Power Query: Searching Questions
4 November 2020
Welcome to our Power Query blog. This week, I answer some searching questions.
I have some tent data and some search strings…
I begin by extracting my tent data to Power Query, using the ‘From Table’ option on the ‘Get & Transform’ section of the Data tab.
I call my query ‘TentData’. I go back to my spreadsheet and create a query for my search strings.
I call my query ‘SearchStrings’ and choose ‘Convert to List’ from the Transform tab.
I am now ready to use my list to search the tent data. I go back to my tent data query. I am going to enter a new step.
The M code I have entered is:
= Table.AddColumn(Source, "Matched",
(CheckStrings) =>
List.AnyTrue(List.Transform(SearchStrings, each Text.Contains((CheckStrings[Included]),_ ))))
In order to understand this step, it needs to be broken down. The start is simple enough; I am adding a new column to my table called Matched. The M function I am using for this is Table.AddColumn():
Table.AddColumn(table as table, newColumnName as text, columnGenerator as function, optional columnType as nullable type) as table.
I am specifying the columnGenerator to provide the function to populate my new column. At the other end, I am using Text.Contains() to check each line of my list SearchStrings against the Included column in my tent data.
Text.Contains(text as nullable text, substring as text, optional comparer as nullable function) as nullable logical
Today, I am not using the optional comparer, but I will look at this next week.
To indicate that each entry on SearchStrings is to be used, I use underscore (‘_’). Text.Contains() is either TRUE or FALSE, so I then create a list of TRUE’s and FALSE’s by using List.Transform().
List.Transform(list as list, transform as function) as list
This returns a new list of values by applying the transform function transform to the list.
Finally, I check if any TRUE’s are found using List.AnyTrue().
List.AnyTrue(list as list) as logical
This returns true if any expression in the list is true.
If there are, the new column is populated with ‘TRUE’. The results I get are,
which, if I look at my search list, is correct:
The word ‘awning’ appears in the ‘Wedding’ package, so that is TRUE. The word ‘toy’ appears in the ‘Childrens’ package, so that is TRUE. Since I haven’t indicated that case should be ignored, the word ‘bar’ does appear in the ‘Party’ package, but it’s not the right case so that is FALSE. I will look how to tweak my query to ignore case next time. I will also look at how to make exact matches, which means that ‘awning’ and ‘awnings’ would not be picked up as a match.
Come back next time for more ways to use Power Query!
