BLOGS BY TOPIC▼
BLOGS BY AUTHOR▼
BLOGS BY YEAR▼
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:
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!