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 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;
Finding enumerators is also easy with help of #shared keyword. Here I can see enumerators for JoinKind and JoinAlgorithm;
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.
6 thoughts on “Power Query Library of Functions; Shared Keyword”
Thanks for sharing. This will invariably save me time and effort. However, I have 2 questions:
1. What is your practice for managing user-defined functions. What I mean is I would expect to see these in the current workbook but I want to re-use the functions in other BI solutions and Excel books.
2. Where is #shared and other PQ keywords documented, like #duration?
Thanks for your kind words.
one way of sharing is through Data Catalog. Read this article: https://support.office.com/en-us/article/View-and-update-a-shared-query-Power-Query-ece958c5-baf2-4789-9312-bb113e679e91
for a reference for PQ keywords, data types, and few others, there is a PDF document, a bit old, but still very useful here: http://pqreference.azurewebsites.net/PowerQueryFormulaLanguageSpecificationAugust2015.pdf
Great tip. I have been struggling with the “M” Function reference page since the page with all functions was changed for a function category index.
This is gold! I wish I knew this before. It is very helpful for M beginners and great reference! Thank you very much!
Thank you so much. It is really very helpful.