Secret Power Query Function List

Hidden inside both Excel and Power BI is a secret Power Query function list that you can bring up while working in the Power Query editor window…that’s if you know the magic word. You can use this list much like you might use the Excel Function wizard or to create a custom function.

Clicking the white space beside ‘function’ will bring up the function explanation in a pane at the bottom of the Power Query editor window. Alternatively, click on the green ‘Function’ text in the Value column to invoke the function.

Watch the Video

Download Workbook

Power Query Function List

To expose the Power Query function list, enter =#shared in the formula bar and press enter (either in an existing query or a new blank query):

This results in a list of functions you can browse through and get further information, however, in this view they’re not sorted alphabetically. To remedy that, click the ‘Convert into table’ button on the Record Tools Convert tab and from there you can filter and sort:

Using the Power Query Function List

There are two ways to use this list, either as a reference to save you going to Google (or Bing) and searching for the function you’re after, or to create a custom function for use in other queries.

To create a custom function, click on the green ‘Function’ text and then give that query a name. For example, I’ve created a custom function for the Date.IsInCurrentYear function which returns a TRUE value if the date is in the current year based on the system time and FALSE if not. I’ve called the custom function ‘DateIsInCurrentYear’:

I’ve also loaded a table containing a list of dates in a separate query called DateTable:

And then via the Add Column tab in the DateTable query I’ve invoked the custom function:

With the column name ‘Current Year’ and referencing the DateIsInCurrentYear function which references the ‘Date’ column of my table:

The end result is a column containing TRUE/FALSE values:

So, you can see it’s fairly straight forward to use functions via the Shared list as a custom function, but keep in mind that it might be cleaner to simply use the shared list as a reference rather than clutter up your file by creating lots of custom functions that aren’t really required.