This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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

In this blog

Setting the scene
Listing all M functions
Turning this list into a usable table
Creating a custom function

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:

Click here to show the syntax of the function.

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.

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:

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!

Some other pages relevant to the above blog include:

This blog has 0 threads

Add a new post

BLOGS BY TOPIC

BLOGS BY AUTHOR

The Magic of the Power Query SHARED Global Environment

Setting the scene

Listing all M functions

Turning this list into a usable table

Creating a custom function

Head office

London

Manchester