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:
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
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
Use the 2016’s web address in the From Web page;
In the Navigator you can see that Table 0 is the table containing the data we are after. Select this table and click Edit.
This will open Query Editor Window for you, You can now make some changes in the query, For example remove the first column.
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.
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.
After creating the Parameter you can see that in Queries pane with specific icon for parameter.
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.
Name the Custom column as Year, and write the expression to be equal Year (remember names in Power Query are case sensitive)
Now you can see year value added to the table. I have also changed its data type to be Text
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
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.
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;
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.
Name the function as GetHolidays and click on OK.
You will now see a group (folder) created with name of GetHolidays including 3 objects; main query (Table 0), Parameter (year), and function (GetHolidays).
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:
= List.Numbers(2015,5)
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.
This is a List, and can be converted to Table simple from List Tools menu option.
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.
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.
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.
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);
Now if you go back to Query1, you will see changes in the results table immediately.
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:
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;
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;
and here is the final result;
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.
VIDEO
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 Nir,
Thanks for your kind feedback.
In Power Query nothing is impossible talking about custom functions. You can pass another query as the parameter to a function, or you can return a record or table as a result of a function. here is an example:
https://radacad.com/power-query-function-that-returns-multiple-values
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 Ashish.
As you said URL Parameters won’t work when you publish Power BI report into Power BI Service. depends on the situation there might be some workarounds. for example here is a workaround for looping through files in a table (Combine Binaries used instead of custom function with parameters):
https://radacad.com/loop-through-on-premises-files-with-power-bi-and-schedule-it-to-refresh-automatically
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:
https://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
I was searching for this kind of information for few months and finally I came across and every line on this page has benefitted me.
Just writing Thank you cannot be justified.
Very good
I have followed this video and it worked very well
just would ask how to extract text with its hyperlink
Thank You
Hi Youcef
Power Query recently has the option to fetch URLs using the Table By Example section of the Navigator window. If that doesn’t work for you, then this is another approach that I use a lot myself.
Cheers
Reza
Good one
Reza, thank you for this beautiful article. It took me many months to be able to understand this piece in-depth. As I did further transformation in Table 0 to arrive at the same “answer” as you do in “The Example At The End” segment, I realized when I go to Query1 and expanded my data, those data types are reverted to ABC123 (which isn’t what I defined in Table 0). Is this a bug/limitation in Power BI?
Hi Alex.
Thanks for your kind words.
there are certain transformations in Power Query that doesn’t inherit the underlying table column data types. Especially when you expand you will see this behavior.
Cheers
Reza