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 😉
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!
Please try the following: Text.Contains(Text.Combine(List.Combine(Table.ToRows(Source)), “|”), “INCOME STATEMENT”)
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.
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