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
The first parameter “MyTable” refers to the table to search through
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.
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.
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.
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.
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:
If you’re working with large data or complex queries that take a long time refresh, cancelling one of those refreshes can even take longer time, especially, if the query has run for quite some time already.
Luckily, there is an easy trick to cancel refresh without loosing the work you’ve done already:
The function below has just one parameter where you either fill in a full filename (incl. path) of an Excel file, or a folder path where multiple files reside. The function will automatically detect the right modus and spit out the M-code. Read more
Today I’ll present an adjustment to the Text.SplitAny – function in Power BI’s query editor or Power Query. The native function takes a string as an input and splits the text by every character that is contained in the string. This seems fairly unusual to me and I haven’t used that function very often.
But what I have come across fairly often is the requirement to split a string by a bunch of different (whole) strings (instead of single characters).