562 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
|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.|
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!
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.
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.
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.
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!
25 Aytoun Street