As I mentioned earlier in Power BI online book, Power Query is a functional language. Knowing functions is your best helper when you work with a functional language. Fortunately Power Query both in Excel and Power BI can use shared keyword to reveal a document library of all functions. I’ve written about shared keyword almost 2.5 years ago, when it was only an add-in for excel. However I still see people in my webinars who are new with #shared keyword functionality and amazed how helpful this little keyword is. So I decided to explain it with the new Power BI. With the method in this post you can find any function you want easily in Power Query, and you won’t need an internet connection to search in functions.

#shared Keyword

Shared is a keyword that loads all functions, and enumerators in the result set. You can simply create a blank query in Power BI (or Excel)

After opening a blank query, go to Advanced Editor

Here in the script editor is where you usually write or modify power query script. Now delete all script here and only type in: #shared

After clicking on Done, you will see list of all functions, and enumerators in power query. You will also see other queries in your Power BI solution or workbook plus other custom functions.

Result set here is a Record structure that has functions, enumerators, and queries in each item of the record. Now let’s explore the record more in details.

Use the Result Set as a Table

Result above is loaded in Power Query and that is the greatest feature of Power Query itself that can turn this result into a table.

This will convert result set record to a table. and table is really easy to search in as you know.

I can now simply search in the function list. Let’s see for example what function I can find for working with WEEK;

This will filter the table to a sub set that I can see only functions with WEEK in the name of it.

Documentation of Function

Now for example if I want to see the Date.AddWeeks function I can click on the “function” link of it in value column and this will redirect me to the documentation of this function, and will bring a dialog box to invoke the function!

You can see the documentation in grey that also includes examples of how to call this function. for invoking the function I can simply provide parameters, and click OK or Invoke;

And it calls the function and show me the result as below;

Enumerators

Finding enumerators is also easy with help of #shared keyword. Here I can see enumerators for JoinKind and JoinAlgorithm;

Summary

You’ve learned how #shared keyword can be helpful for getting list of all functions and enumerators in Power Query. You learned you can convert the result set into a table and filter that to find particular function you are looking for. This is superb, especially for people like myself who can’t remember things well. You know how I look for functions? this post explained my method! I use #shared keyword to find the function I want and start working on that. for me #shared is the keyword that I use more than any other queries in Power query side of Power BI.

Save

Save

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.