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

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).

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 12 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, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.