Power Query Library of Functions; Shared Keyword

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

2016-06-20_08h47_59

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)

2016-06-20_08h42_31

After opening a blank query, go to Advanced Editor

2016-06-20_08h44_00

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

2016-06-20_08h45_36

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.

2016-06-20_08h47_59

 

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.

2016-06-20_08h50_57

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

2016-06-20_08h52_39

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

2016-06-20_08h55_04

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

2016-06-20_08h56_31

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!

2016-06-20_08h58_22

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;

2016-06-20_09h06_18

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

2016-06-20_09h07_21

Enumerators

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

2016-06-20_09h11_46

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

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
rssyoutuberssyoutube
Reza Rad
Reza Rad is an Author, Trainer, Speaker and DW/BI Consultant. He has a BSc in Computer engineering; he has more than 15 years’ experience in databases, programming and development mostly on Microsoft technologies. He is a Microsoft MVP in Data Platform for seven continues years (from 2011 till now) for his dedication in Microsoft BI. He is author of some SQL Server and BI books, and also Power BI online book; from Rookie to Rock Star.

4 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">