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.

Read more

How to get more out of your Graph API custom connector in Power BI

The Graph API can deliver a huge amount of interesting data from your Microsoft 365-universe, but the Graph API custom connector for Power BI is not able to retrieve everything from it in its current shape. So I’ve modified it a bit to squeeze out a bit more of its sweet juice.

Problem

When trying to get the details for planner tasks, the following error-message appears:

Error in Graph API custom connector when retrieving details from planner tasks

Solution

Read more

Bulk-extract Power Query M-code from multiple Excel files at once

Some time ago I published a function that extracts all M-code from Power BI (.pbix)-files. Today I publish the pendant to Bulk-extract Power Query M-code from multiple Excel-files at once. The code contains many elements from the before mentioned, so please refer to that article for reference.

How to use

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

Writing data to GitHub using Power Query only

You shouldn’t do it …

Generally it’s a very bad idea to execute commands in Power Query that write out data to a source, as these commands might be executed multiple times (https://blog.crossjoin.co.uk/2018/10/07/power-query-write-data/)

… unless … maybe ?

However, as with most good rules, there are exceptions. I leave it to you to decide whether my use case here is a valid candidate for it. It doesn’t execute the code twice, because I execute the query only from the query editor and none of the other queries is referencing its results. But please see for yourself – Writing data to GitHub using just Power Query:

The video

Read more