Custom Functions Made Easy in Power BI Desktop

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

2016-12-06_17h51_22

I have written a lot about Power Query M scripting language, and how to create custom functions with that. With recent updates of Power BI Desktop, creating custom functions made easier and easier every month. This started with bringing Parameters few months ago, and adding source query for the function in November update of Power BI Desktop. In this blog post you will learn how easy is to create a custom function now, what are benefits of doing it in this way, and limitations of it. If you like to learn more about Power BI; read Power BI online book from Rookie to Rock Star.

What is Custom Function?

Custom Function in simple definition is a query that run by other queries. The main benefit of having a query to run by other queries is that you can repeat a number of steps on the same data structure. Let’s see that as an example: Website below listed public holidays in New Zealand:

http://publicholiday.co.nz/

For every year, there is a page, and pages are similar to each other, each page contains a table of dates and descriptions of holidays. here is for example public holidays of 2016:

http://publicholiday.co.nz/nz-public-holidays-2016.html

2016-12-06_15h10_48

You can simply use Power BI Get Data from Web menu option of Power Query to get the public holidays of this page. You can also make some changes in the date format to make it proper Date data type. Then you probably want to apply same steps on all other pages for other years (2015, 2017, 2018…), So instead of repeating the process you can reuse an existing query. Here is where Custom Function comes to help.

Benefits of Custom Function

  • Re-Use of Code
  • Increasing Consistency
  • Reducing Redundancy

With a Custom function you are able to re-use a query multiple times. If you want to change part of it, there is only one place to make that change, instead of multiple copies of that. You can call this function from everywhere in your code. and you are reducing redundant steps which normally causes extra maintenance of the code.

How to Create Custom Function?

Well, that’s the question I want to answer in this post. Previously (about a year ago), creating custom functions was only possible through M scripting, with lambda expressions, that method still works, but you need to be comfortable with writing M script to use that method (To be honest I am still fan of that method ;) ). Recently Power BI Desktop changed a lot, you can now create a function without writing any single line of code.

Through an example I’ll show you how to create a custom function. This example is fetching all public holidays from the website above, and appending them all in a single table in Power Query. We want the process to be dynamic, so if a new year’s data appear in that page, that will be included as well. Let’s see how it works.

Building the Main Query

For creating a custom function you always need to build a main query and then convert that to a function. Our main query is a query that will be repeated later on by calling from other queries. In this example our main query is the query that process the holidays table in every page, and return that in proper format of Date data type and Text for description of holiday. We can do that for one of the years (doesn’t matter which one). I start with year 2016 which has this url:

http://publicholiday.co.nz/nz-public-holidays-2016.html

Open a Power BI Desktop and start by Get Data from Web

2016-12-06_15h34_16

Use the 2016’s web address in the From Web page;

2016-12-06_15h35_16

In the Navigator you can see that Table 0 is the table containing the data we are after. Select this table and click Edit.

2016-12-06_15h36_01

This will open Query Editor Window for you, You can now make some changes in the query, For example remove the first column.

2016-12-06_15h37_51

First column was an empty column which we removed. Now you can see two columns; Holiday (which is description of holiday), and Date. Date column is Text data type, and it doesn’t have year part in it. If you try to convert it to Date data type you will either get an error in each cell or incorrect date as a result (depends on locale setting of you computer). To convert this text to a date format we need to bring a Year value in the query. The year value for this query can be statistically set to 2016. But because we want to make it dynamic so let’s use a Parameter. This Parameter later will be used as input of the query.

Parameter Definition

Parameters are ways to pass values to other queries. Normally for custom functions you need to use parameters. Click on Manage Parameters menu option in Query Editor, and select New Parameter.

2016-12-06_15h42_46

There are different types of parameters you can use, but to keep it simple, create a parameter of type Text, with all default selections. set the Current Value to be 2016. and name it as Year.

2016-12-06_15h44_32

After creating the Parameter you can see that in Queries pane with specific icon for parameter.

2016-12-06_15h58_28

Now we can add a column in Table 0 with the value from this parameter. Click on Table 0, and from Add Column menu option, click on Add Custom Column.

2016-12-06_16h39_39

Name the Custom column as Year, and write the expression to be equal Year (remember names in Power Query are case sensitive)

2016-12-06_16h41_02

Now you can see year value added to the table. I have also changed its data type to be Text

2016-12-06_16h45_07

Now that we have created Parameter we can use that parameter as an input to the query’s source URL as well.

URL Parameterization

One of the main benefits of Parameters is that you can use that in a URL. In our case, the URL which contains 2016 as the year, can be dynamic using this parameter. Also for converting a query to a custom function using parameters is one of the main steps. Let’s add Parameter in the source of this query then;

While Table 0 query is selected, in the list of Steps, click on Setting icon for Source step

2016-12-06_17h40_17

This will bring the very first step of the query where we get data from web and provided the URL. Not in the top section change the From Web window to Advanced.

2016-12-06_17h41_29

Advanced option gives you the ability to split the URL into portions. What we want to do is to put Text portions for beginning and end of string, and make the year part of it dynamic coming from URL. So Add another part, and put setting as below;

2016-12-06_17h45_18

Configuration above means that in the first part of URL we put everything before 2016 which is:

http://publicholiday.co.nz/nz-public-holidays-

Second part of URL is coming from parameter, and we use Year parameter for that

third part of URL is the remaining part after the year which is:

.html

altogether these will make the URL highlighted above which instead of {Year} it will have 2016, or 2015 or other values.

Click on OK. You won’t see any changes yet, even if you click on the last step of this query, because we have used same year code as the parameter value. If you change the parameter value and refresh the query you will see changes, but we don’t want to do it in this way.

Convert Query to Function

After using parameter in the source of query we can convert it to function. Right click on the Table 0 query and select Create Function.

2016-12-06_17h49_11

Name the function as GetHolidays and click on OK.

2016-12-06_17h49_57

You will now see a group (folder) created with name of GetHolidays including 3 objects; main query (Table 0), Parameter (year), and function (GetHolidays).

2016-12-06_17h51_22

The function itself marked with fx icon and that is the function we will use to call from other queries. However the main query and parameter are still necessary for making changes in the function. I will explain this part later.

All happened here is that there is a copy of the Table 0 query created as a function. and every time you call this function with an input parameter (which will be year value) this will give you the result (which is public holidays table for that year). Let’s now consume this table from another query, but before that let’s create a query that includes list of years.

Using Generator

Generators are a topic of its own and can’t be discussed in this post. All I can tell you for now is that Generators are functions that generate a list. This can be used for creating loop structure in Power Query. I’ll write about that in another post. For this example I want to create a list of numbers from 2015 for 5 years. So I’ll use List.Numbers generator function for that. In your Query Editor Window, create a New Source from Home tab, and choose Blank Query.

This will create a Query1 for you. Click on Query1 in Queries pane, and in the Formula bar type in below script:

2016-12-06_18h06_42

After entering the expression press Enter and you will see a list generated from 2015 for 5 numbers. That’s the work done by a generator function.

2016-12-06_18h09_57

This is a List, and can be converted to Table simple from List Tools menu option.

2016-12-06_18h10_37

Convert this to Table with all default settings, and now you can a table with Column1 which is year value. Because the value is whole number I have changed it to Text as well (to match the data type of parameter).

Consuming Function

It is easy to consume a function in Query Editor from a table. Go to Add Columns, and click on Invoke Custom Function option.

2016-12-06_18h12_53

In the Invoke Custom Function window, choose the function (named GetHolidays), the input parameter is from the table column name Column1, and name the output column as Holidays.

2016-12-06_18h14_10

Now when you click on OK, you will see a new column added with a table in each cell. These tables are results of calling that function with the input parameter which is value of Column1 in each row. If you click on a blank area of a cell with Table hyperlink, you will see the table structure below it.

2016-12-06_18h15_46

Interesting, isn’t it? It was so easy. All done from GUI, not a single line of code to run this function or pass parameters, things made easy with custom functions.

Editing Function

If you want to make modifications in function, you can simply modify the main query (which is Table 0 Example). For example let’s create a full date format from that query in this way;

Click on Table 0 Example query and split the Date column with delimiter Comma, you will end up having a column now for Month values and another for Day (Note that I have renamed this columns respectively);

2016-12-06_18h25_31

Now if you go back to Query1, you will see changes in the results table immediately.

2016-12-06_18h26_15

Limitations

Edit Script for the Function

Changes are fine as long as you don’t want to edit the M script of the function, if you want to do so, then the function definition and the query definition split apart. and you will get below message:

2016-12-06_18h28_18

It is alright to make changes in the advanced Editor of the source query, and then the function will be updated based on that, but if you want to change the function itself, then the query will be separated.

Disable Load of the Source Query

If you have read my blog post about Enable Load in Power Query, you already know that queries that is not used in the model should not be loaded. By Default the source query (in this example named as Table 0 Example) will be loaded into the model. This means one extra table, and consuming more memory. So remember to uncheck the Enable Load for this query;

2016-12-06_18h37_06

Parameterized URLs

Custom Functions that uses parameterized URLs (like this example) cannot be scheduled to refresh in Power BI. That’s a big limitation which I hope be lifted very quickly.

The Example at the End

I have done some other changes, and changed the data type to Date format. Now the final query which is expanded table from all underlying queries include all public holidays. I’ll leave that part to you to continue and build rest of the example. For that functionality you need to use Expand;

2016-12-06_18h40_20

and here is the final result;

2016-12-06_18h41_25

Summary

In this post you have learned how easy is to create a custom function from a query, all from graphical interface. You have seen that I haven’t wrote any single line of code to do it. and all happened through GUI. You have seen that you can change definition of function simply by changing the source query. and you also have seen how easy is to call/consume a function from another query. This method can be used a lot in real world Power Query scenarios.

 

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

8 Comments

  • Dear Radacad.
    Great post. Love to read your comprehensive explanation.
    Can I Prepare function as you made that take 3 parameters:
    1- column name
    2- start point
    3- number of characters
    I wand to break big txt file to columns based on table I already have(3 columns that represent the 3 parameter).
    Thanks in advance.
    Nir.

  • Hi Reza,

    Thank you for writing another nice blog. I am planning to implement it just want to check if there are any work around for parameter option, as it work only in desktop not in web.If there are some work around please share.

    Thank you once again.
    Regards
    Ashish Gupta

  • Hi Reza,

    Thank you for sharing this blog, actually I am looking some option as parameter work in desktop give option to select value and filter data only display on dashboard. Similar way it has to worked in Power BI Web or Published file. If we have 5 different country data and by selecting first, it filter data and only for that county on report/dashboard (Some what like prompt option in Microstrategy).

    Regards
    Ashish Gupta

    • Hi Ashish,
      What you want is possible through the Power BI visuals and slicers itself. You don’t need Power Query Custom Function for that. By default every visual in your report will be showing you filtered data when you select country in slicer. If you don’t want to use slicer and you want to use normal visuals for filtering down, then you can control the Interaction simply, here is how:
      http://radacad.com/control-the-interaction-in-power-bi-report

      • Hi Reza,

        Thank you once again for reply but this is not what I am looking. I am looking for a dynamic filter(Please check Prompt functionality in MSTR) in Power BI web, User should have option to select value from dropdown. If I have 6 sales person and assigned one/two role(map to county) to every one based on different country. Once they will login run a dashboard in web, it will display parameter and option to select value from dropdown. As currently functionality working in desktop once run dashboard shows a pop up for selection of country.Hope I am clear now regarding my question.
        I am referring below link for desktop to achieve that functionality but once published dashboard, its not working.
        URL: https://powerbi.microsoft.com/en-us/blog/deep-dive-into-query-parameters-and-power-bi-templates/

        Thank you.

        Regards
        Ashish Gupta

        • Hi Ashish,
          URL parameters is not supported in Power BI service to refresh YET. That’s why I mentioned to you that as a workaround load all of the data and use filters in report to filter the view. if this is not clear yet, please contact to my email address and we will discuss that more in details.
          Cheers
          Reza

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="">