A new Table.ContainsAnywhere function for Power Query in Power BI and Excel

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 😉

Comments (2) Write a comment

  1. Pingback: Searching For Text Across All Columns in Power BI – Curated SQL

  2. Pingback: The BIccountant

Leave a Reply

Die Datenschutzbestimmungen finden Sie hier: / Please find the privacy policy here: https://wp.me/P6lgsG-Rz