The full Table.ContainsAnywhere function for Power Query in Power BI and Excel

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

  1.  The first parameter “MyTable” refers to the table to search through
  2.  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.
  3.  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.
  4.  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.
  5.  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

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 😉

Comments (2) Write a comment

  1. 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}))

    Reply

    • 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

      Reply

Leave a Reply

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