WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
Wise Owl Training
See 519 reviews for our classroom and online training
If you found this blog useful and youâ€™d like to say thanks you can click here to make a contribution. Thanks for looking at our blogs!

BLOGS BY TOPIC

BLOGS BY AUTHOR

BLOGS BY YEAR

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:

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:

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:

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:

Well, almost alphabetical ...

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

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:

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

You could now rename this query:

I've called my query M functions.

You could also sort the functions alphabetically:

Choose the option shown to sort your functions by name.

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

The options to choose.

Search for the word 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:

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:

Power BI shows the syntax of this 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:

Here I've called my function AgeInDays.

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

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:

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!