In a previous post I introduced the concept of a function that searches for an occurrence of a character or string within all columns of a table. Here I share the full “Table.ContainsAnywhere” – function with parameters for many useful options.
Function parameters and options
- The first parameter “MyTable” refers to the table to search through
- The 2nd parameter “MySearchStrings” can be either a text field or a list of strings to be searched for. The function will take care of any of these cases automatically.
- If the 2nd parameter is a list and this 3rd parameter is null or not speified, the function will return true if any of the list items is found within the table. But if set to “All”, all list items have to be found somewhere in the table for the function to return true.
- By default, the search will be made in a case sensitive mode (as this is the default-mode in Power Query). But any entry into the 4th function parameter will turn this to a case insensitive mode instead.
- By default, the string or list entry has to match fully with any entry in the table. Again, any entry in the 5th parameter swaps that to a partial match.
Function code
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| let func = | |
| (MyTable as table, | |
| MySearchStrings as any, | |
| optional AllAny as text, | |
| optional CaseInsensitive as text, | |
| optional PartialMatch as text ) => | |
| let | |
| /* Debug Parameters ___Description for function parameters | |
| MyTable = MyTable, // Table to search trough | |
| MySearchStrings = {"INCOME STATEMENTs"}, // Can be entered as text for a single search string or as a list of search strings | |
| AllAny = null, // optional: If list of search strings, true will be returned if any of the search strings are included. –> | |
| // Can be set to "All" and then all search strings must be included somewhere in the table | |
| CaseInsensitive = null, // Case sensitivity is the default-mode, but any value entered here will change to case insensitive mode | |
| PartialMatch = "x", // By default the values of the cells must match the search strings fully. But a parameter here will switch to partial match. | |
| */ //End of Debug Parameters | |
| // Creates a variable for the type of the search string | |
| typeSearchStrings = if Value.Is(MySearchStrings, type text) then "Text" else "List", | |
| // *** 1) Create functon-modules that can later be executed sequentially *** | |
| // If the search string is text, then a functions will simply be applied to it, | |
| // but if it is a list, then the function has to be applied to each element of the list (using List.Transform) | |
| fnTextOrList = Record.Field([ Text = (x, fn) => fn(x), | |
| List = (x, fn) => List.Transform(x, fn)], | |
| typeSearchStrings), | |
| // If the search string is text, then these option are irrelevant (and shall be ignored, in case there are any entries for it) | |
| // but if it is a list, then the respective function shall be choosen. Default-value is "Any". | |
| fnAllAny = if typeSearchStrings = "Text" | |
| then (x) => x | |
| else Record.Field([ All = List.AllTrue, | |
| Any = List.AnyTrue], | |
| if AllAny = null then "Any" else Text.Proper(AllAny)), // Default is "Any" | |
| // Transforms to lower if CaseInsensitive, otherwise leaves the value unchanged | |
| fnCaseInsensitive = if CaseInsensitive = null | |
| then (x) => x | |
| else (x) => Text.Lower(x), | |
| // The default-value for PartialMatch is false and in that case, a list item has to match the search string completely (List.Contains), | |
| // but if there is an entry in "PartialMatch", then each item in the list has to be checked if it contains any of the search strings: | |
| // List.Transform iterates through the TableList and checks each of its items if it contains the string (Text.Contains) | |
| fnPartialMatch = if PartialMatch = null | |
| then (x) => List.Contains(AdjustTableListToCaseSensitivity,x) | |
| else (x) => List.AnyTrue(List.Transform(AdjustTableListToCaseSensitivity, (z) => Text.Contains(z, x))), | |
| // *** 2) Execute function-modules sequentially *** | |
| TransformTableToList = List.Combine(Table.ToRows(MyTable)), | |
| AdjustTableListToCaseSensitivity = List.Transform(TransformTableToList, fnCaseInsensitive), | |
| AdjustSearchStringsToCaseSensitivity = fnTextOrList(MySearchStrings, fnCaseInsensitive), | |
| CheckForMatches = fnTextOrList(AdjustSearchStringsToCaseSensitivity, fnPartialMatch), | |
| ChooseIfAllOrAny = fnAllAny(CheckForMatches) | |
| in | |
| ChooseIfAllOrAny , | |
| documentation = [ | |
| Documentation.Name = " Table.ContainsAnywhere.pq", | |
| Documentation.Description = " Checks if a string or list of strings is contained somewhere in the table. ", | |
| Documentation.LongDescription = " Checks if a string or list of strings is contained somewhere in the table. <code>AllAny</code> parameter accepts ""All"" if all search parameters from the list must be found. <code>CaseInsensitive</code> parameter accepts any entry to change the default case sensitive mode to case insensitive instead. <code>PartialMatch</code> accepts any entry to change from the default full match requirement to a partial match. ", | |
| Documentation.Category = " Table ", | |
| Documentation.Source = " www.TheBIccountant.com . https://wp.me/p6lgsG-14c . ", | |
| Documentation.Version = " 1.0 ", | |
| Documentation.Author = " Imke Feldmann: www.TheBIccountant.com. https://wp.me/p6lgsG-14c . ", | |
| Documentation.Examples = {[Description = " ", | |
| Code = " Table.ContainsAnywhere.pq(#table( {""Class"", ""Name""}, List.Zip( { {""Fruit"" ,""Fruit"" ,""Vegetable""}, {""Pear"" ,""Pineapple"" ,""Cucumber""} } ) ), {""Apple"", ""Pear""}, ""All"", ""x"", ""y"") ", | |
| Result = " true "]}] | |
| in | |
| Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation)) |
I encourage friends of the M-language to read through the documented code of the “Table.ContainsAnywhere”-function. It shows a fairly compact way to handle the 24 different functions that are needed for all possible function parameter combinations. For each parameter, I created one function module that covers the part of the function-logic that is specific to this parameter. These function modules also carry the case selection already. So they will deliver just what’s needed to the main query part (2), where they can then be executed sequentially. This way I avoid heavy branching with if-then-else-statements and redundant code.
Enjoy and stay queryious 😉
That is a neat way to specify functions using parameters (Record.Field). I will have to add that to my bag of tricks.
Is there a need to add a line to ensure that all the columns of MyTable have the text data type prior to searching?
Table.TransformColumnTypes(MyTable, List.Transform(Table.ColumnNames(MyTable), each {_, type text}))
Thanks Jeremy,
that’s a good point: If you want to include numbers or dates to be searched as strings, you should do such a transformation!
Cheers, Imke