How to Get List of All Functions and Invoke Them in Power Query

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

Yesterday I’ve found a very awesome feature in Power Query (Thanks to Matt Masson). The feature is: you can see list of all functions in the Power Query with enumerations and option types with a keyword. I’m personally coming from a developer background, so that option for me looks like the Object Explorer in Visual Studio where you can see all objects and classes, their functions and properties with description. Here in Power Query you can see functions and options with their explanation, and even one step further; you can invoke any of functions from the GUI there.

So I thought that this worth to share in a separate blog post. Here is how to get list of all functions;

Get List of All functions

In the Advanced Query window simply type in #Shared . This keyword will list all functions in the Query Editor window as below:

If you scroll down the list you will also see enumeration options such as JoinKind (which we talked about that in previous posts);

Invoke

You can invoke any of functions with clicking on the Function link in front of the function as below screenshot shows:

Then you will be redirected to a new tab in Query Editor, named Invoke Function. here you can see description of the function, and also input parameters.

If you enter required information, then you will see the result

result:

#;this character is the escape character in M, and also it used before keywords in M. for example we use that with keywords as following: #table, #datetime, #shared….

The #shared keyword not only shows list of all functions, but also it is an intrinsic variable that lists all shared section members currently in scope (in future posts I’ll describe that you can define multiple sections in M, and in each section you can define private or shared variables. #shared will list all shared variables of sections in scope).

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.

3 thoughts on “How to Get List of All Functions and Invoke Them in Power Query

  • It seems that #shared doesn’t work in Power BI (and works only on PBIDesctop and Excel).
    I have empty pbix with only one Query, which is Source = #shared, and I get this error, if I try to refresh the Dataset from the pbix:
    “You can’t schedule refresh for this dataset because the following data sources currently don’t support refresh:
    Data source for Query1”

    • Hi Andrey
      Yes, As mentioned in my reply to your previous comment; #shared is an environment variable let’s say, and it is not like other variables to work as an auto refresh in the website. The main usage of that is in Power Query on your machine.
      Cheers
      Reza

Leave a Reply

%d bloggers like this: