The native Table.Contains-function in Power Query tells you if one or more strings are included in one or more of its columns. But you have to be specific about which strings you search in which column. But what to do if you want to search a string in all of its columns instead? Use my new Table.ContainsAnywhere function.
In the native function, you have to pass in a record with search term and column name. So if you search for “blue” in column “Description”, your formula would look like so:
Table.Contains( YourTableName, [Description = "blue"] )
But that’s not what I want in this case. I want the formula to search through all columns within the table for the occurrence of “blue”.
One way would be to transform the list of column names of the table to a nested list where for each column name, the search-string would be added. But that gets a bit clumsy if you want to use it in a Table.AddColumn-step. So I’m going a different path instead:
Say this is my table and I want to know if the string “INCOME STATEMENT” is included in any of its fields:
1. Split the table into a list of lists where each list contains all fields from one row:
2. Combine that list into one, means you have all fields of the table in one big list:
3. Check if this list contains the search term:
List.Contains(List.Combine(Table.ToRows(Source)), "INCOME STATEMENT")
I’m using it to catch some specific tables from SEC-filings for example. This is the result of a Pdf.Tables-function to extract quarterly report data from a large pdf file. It shows all the different page- and table elements in it:
I’ve added a column with the function above and can now filter on “true” to extract the matching tables.
You want an case-insenstive match? Or search for multiple strings? And be able to distinguish between any and all-matches? Or even go for partial matches?
Then watch out for the next article where you get the function with all bells and whistles.
Enjoy & stay queryious 😉