Browse 547 attributed reviews, viewable separately for our classroom and online training
The Magic of the Power Query SHARED Global Environment
How to use the #shared keyword to bring up a list of all of the M functions in Power BI Query, and then use this list to create your own help table or even your own function.

Posted by Andy Brown on 26 July 2021

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

The Magic of the Power Query SHARED Global Environment

You can use the #shared keyword in Power Query to get a list of all of the functions in M, and even use this technique to create your own function!

Setting the scene

I've loaded a list of the 10 oldest people in the world from Wikipedia into Power Bi:

Oldest people

I've taken out the gender column, as they were all (to a woman) female.

I then did a bit of manipulation to get the start and end date for each person (two people are at the time of writing still alive, so I've used today's date for an end point - Monday 26th July 2021).  This gives this:

Revised dates

Everyone now has a start and an end date; what I now want to do is to calculate the difference between them in days.

Listing all M functions

Here's one way to get at a list of all M functions using the #shared keyword.  First create a new query in Power BI Query Editor:

Creating blank query

Click on the option shown to create a new source.

 

Type in =shared. and you will see an alphabetical list of all M functions appear:

List of functions

Well, almost alphabetical ...

Delete the . character at the end so you just have =#shared in the formula bar, then press the Enter key:

List of functions

You now have a list of all of the tables, functions and other objects in M.

Turning this list into a usable table

Now click on the Into Table button shown selected above (and below) to store this list as a table:

Into table button

Click on this button to create a table from your list of functions.

 

You could now rename this query:

Renaming query

I've called my query M functions.

You could also sort the functions alphabetically:

Sorting functions

Choose the option shown to sort your functions by name.

Choose to filter your list to show only functions containing a given phrase:

Filtering list

The options to choose.

Search for the word Duration:

Filtering for duration

I've guessed that the function to show the duration in days of each person's life will contain the word Duration.

M is case-sensitive: if I'd searched for duration instead, the filter wouldn't have returned anything.

You should now have a much shorter list of functions - you can get the syntax for any one by clicking on its Function link:

List of functions

The only functions containing the word Duration.

 

Creating a custom function

Finally (and mainly just for fun) we'll create a function called AgeInDays which gives the difference between two dates in days, replicating the existing Duration.Days function.  First, click on the Function link for the Duration.Days  row:

Click on Function

Click here to show the syntax of the function.

Power BI shows the syntax of this function:

Syntax of function

Choose Cancel to clear the pop-up dialog box.

You now have a query which returns exactly one function, so you can rename it to something appropriate:

Renaming query

Here I've called my function AgeInDays.

 
New function

Your list of queries should now show your new function.

 

You can now add a new custom column to your Oldest people (or any other) table:

Adding a custom column

Click on the table, then choose to add a custom column.

The AgeInDays function will behave exactly like the Duration.Days function on which it's based:

Age in days

A formula using your new function to work out each person's age in days.

 

Whether it's worth creating new versions of existing functions like this is debatable, but the idea behind this blog is just to give you ideas of things to try!

This blog has 0 threads Add post