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.
Problem
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”.
Solution
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:
Table to search all columns for a specific string
1. Split the table into a list of lists where each list contains all fields from one row:
Table.ToRows(Source)
Table.ToRows creates one list per row in a nested list
2. Combine that list into one, means you have all fields of the table in one big list:
List.Combine(Table.ToRows(Source))
Combine list of nested list into one (expanded) list
3. Check if this list contains the search term:
List.Contains(List.Combine(Table.ToRows(Source)), "INCOME STATEMENT")
Use cases
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:
Table.ContainsAnywhere function in action
I’ve added a column with the function above and can now filter on “true” to extract the matching tables.
Variations
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 đ
Pingback: Searching For Text Across All Columns in Power BI – Curated SQL
Pingback: The BIccountant
How can you assign the row number of the TRUE record (i.e. page004) to a variable (=9 in this case)?
1) Add an index column (starting at 1)
2) Filter the table on “true” and select the value from the index column.
Thx so much, muito obrigado ajudou bastante.
Dear Imke, thanks a lot. I am loving this function.
I am trying to upgrade this function with a “Text.Contains” (e.g “Income”) searching only for a text string but i doues not work yet.
Maybe you could help me here?
That would be awesome! Thanks a lot in advance!
BR Dennis
Hi Dennis,
Please try the following: Text.Contains(Text.Combine(List.Combine(Table.ToRows(Source)), “|”), “INCOME STATEMENT”)
BR, Imke
Hi,
I have a similar problem I’m trying to solve.
Is it possible to transform a nested table each row of a column A by another nested column or list B?
The transformation being add a column/or just transform to show only the matching rows of the larger column A that match from B.
Each row has the same ‘records’ in A but B has a smaller subset of these records.
explaining further:
I’ve tried
each Table.SelectRows([A], each List.Contains(MyList,[Column in TableA]))
A = nested table
MyList is not a nested table. I was testing if I could get a result. It worked but I don’t know how to swap out the ‘external’ query created MyList for an nested List or table