Custom Functions Made Easy in Power BI

hello everyone I'm Reza red and today I'm going to show you what our custom functions in power curry and how they can be useful in doing a work that you want to do across multiple data sources which has the same structure like creating loop structure going through the multiple files going through multiple tables and importing the same data structure with one single source of code let's go and check it out to show you how custom function works I'm going to show you that through an example the example I'm going to show you is through this website this is a website office holidays come is a website that actually shows you public holidays of most of the countries and it's not let's say a government website to make sure that these are all correct so some public holidays date here might be incorrect I don't say this is good to use in your production environment but it's a good example to use for our demo let's check it out so I'm going to countries under all countries I can go and select the country that I want I'm doing it for New Zealand to find public holidays over there you can do that for your United States or for any other countries you want when I select the country I'll see public holidays of that country and then I can switch between years this is for the current year for 2019 I can change it to 1820 yeah if I have different regions and states in my country I can choose between those to see public holidays specifically for those areas for this example I am going to just another year let's say 2018 and this shows me the public holidays of 2018 and as you can see in the URL it also has 2018 as part of that you are now I can copy this URL and when you copy that make sure that you have the year as part of it somewhere if you don't go change a year to some to another year then you will see the year comes up as part of the URL I'll copy this URL I'll go to the power bi desktop start with get data from web and paste it here and this will scan that URL and find all pages all actually HTML codes and come up with just the tables or HTML tables that you can choose from now custom function is not just for webpages or things like that it can be applied everywhere the example I'm showing it to you is based on a web page but you can do it in any other types of pages as well now I have a number of tables here fetched and they have all kinds of difference data from that page the one that I want here in my example is table 3 in your example it might be another table I'll select that table this is the public holidays of 2018 as you can see here right I'll see this information and then I can go and click on edit this will come up with the preview of data for that period you see I have different columns columns 1 2 3 4 5 and dates I have two separate dates not sure why it came up with two separate dates probably because that there is a column behind this which is hidden I don't see that column but that column is part of this anyway so let's say these are or public holidays right and you might want to apply some transformations on this as well but I'll keep it without any transformations at this stage now I want to go through every single one of these years and and get the public holidays of that year as well but instead of me coming to power curry doing a duplicate or copy of this query every time going to source and changing it to a different URL what I will do is I will create a function from this process my function would be getting the year as the input and providing the table this table as the output public holidays of that year right so in order to create that function I need two elements I need a parameter and I need a query that gets that parameter produced the output so let's start with parameter I'll go to the Home tab under manage parameters I can create a new parameter I'm going to call this year the datatype of year should be number however if I look more in details that where I'm going to use that year value I see that I'm going to use it inside a URL and that URL is a text value right so if I'm going to use it inside the text value it's better to be to take the type of text because otherwise I have to do one date type command data type conversion as well so also it has text I'll set current value as 2018 that's just default value that doesn't matter because we are going to change it so here takes 2018 I click on OK and then the parameter will be added here alright so this is my year parameter this is the value of that if you want to change the parameter this is how you change it 2019 press Enter 2020 press Enter right now when I do these changes it won't impact anything because this parameter is not used anywhere right so let's go and use it in somewhere table 3 which is the table that I used for getting these public holidays has a step source step and that source step has the setting icon and that is where we have the URL of that source so you see this is the URL of that page it has 2018 in it this is the part that I need to change with that prompt I can go ahead and change it into in advanced editor with the query with scripting but this even made it much easier if you click on this Advanced tab you can do this even here without chinga script go to the Advanced tab you'll see URL parts and you'll see the URL preview these parts you can break your URL into multiple parts and you'll see the preview down at the bottom at the moment it's just one part I'm getting 2018 dot PHP I mean 2018 and everything after that the year is everything after that I'll cut that and I'll paste it in the second part I would still have the same preview because it's just breaking into two parts then I'll add a third part which is everything after the year everything after 2018 I'll cut that and I'll paste it here as a third part and still I have the same preview right so it didn't actually change anything at all it just break into three parts with the same output so far now the reason to break it into three parts was that now I'm going to change this part which is the year with a parameter if you have multiple parameters if you have year and state and other things then you need to break it into even more parts than three I'll change this ABC to parameter and it already picks the year because that's the only parameter that I have if it doesn't show you the parameter that you've created the reason is probably because your parameter is not a text datatype so I'll select the parameter and then click OK now if I go to the last step still I'll see 2018 information because parameter value is 2018 if I come here and change it to 2019 this now shows me 2019 public holidays if I go ahead and click on 2015 this would be public holidays of 2015 it will take some time for some of these because it's just query that website again and again so now you see this is public holidays of 2008 and 15 so I've managed to create the parameter and make my query a parametric query that gets the parameter value produced the output right now this query is a good candy to be a function because this query accept a parameter accept an input produce an output right but it's not a function it's still a query to convert the query to function right click on that query and click on create function create function it already knows that I have a parameter input for that it's here and i just put the paw function name so let's say the function name is get holidays click ok now when you create a function a number of changes happens this is those changes first there will be a folder with the name of your function under that folder you'll see three items the parameter used the query used in that function and the function itself right you can go and change the query if you want to roll but how the function itself works the function itself has an input value and it can provide an output and you can test it here like for example let's say I want to call this function for year 2020 invoke whenever you invoke there will be a new query added here with the result of invoking that function with one single step only and that single step is calling this function you see it's equal to get holidays 2020 and khatallah days is this much if I change it to call it for 2010 this would call 2010 and public holidays of 2010 would come right now still this is kind of static process I have to go to every single year and call this one by one right but I managed to create the function right now I'm going to show you a place that you can use this function to make even your even your power query implementation more dynamic if I have a list of these input values like 2010 2011 2020 if I have a list of all of these then I can call this function I can invoke this function for every one of those so let's go ahead and create a list this list is also something that you can fetch from this page itself but let's not talk about that let's just do it statically here so I'll create a new query a new blank query blank quarry is a query that doesn't have any anything in it it's blank basically and I'm going to use formula here if you don't see this formula bar you go ahead in View tab and check the formula bar you'll see that output equal sign and bracket this type of bracket 2010 dot dot 2020 this is saying that the start from 2010 to 2020 one at a time and this will generate a list like this right there are lots of different ways of creating a list this is just one of those so now I have a list of values that I can use as the input of that function right by the way I can remove this invoked function I don't need that because that was just the test so I'll remove the invoke function not the function itself the envelope function okay now in this query I have list of all of these years because I'm going to call it for every one of those years and get the output right beside it I need to change this to a table a list is just a single column structure in the list tools transform I click on compare to table and I'll leave it as the default configuration click on OK now this column one is only one column of a table you see this is a table because the parameter of that function is a text parameter the data type of that is text we need to make sure that this is also a text I'll change this data type to text and now we are ready to invoke that function now to invoke that function for every single value in this column we'll do it this way I'll go to add column invoke custom function then you'll see section here that you can choose what function you want out whose function which is get holidays that's the function that we have over there this can get the input from a value from a column we want this to be a column and we want this to be a column one everything is already defined there and the column name the new column name let's call it holidays so in this part the important parts is function query and the input parameters right so after doing that click on OK now this will take some time because it will go through every one of those years and load output here and at the bottom section you'll see a place that it shows you how many rows load depends on internet connection depends on how busy that website is depends on response time of that website it might take some time and it will work like a web-based crapper we'll go through every one of those years and populate those information now this is not just for a web address this can be files in a folder this can be multiple sheets in one excel file this can be emails in multiple emails in one place this can be all types of structures multiple tables in database multiple databases in a server things like that and it will come up like this so now you see we have a table under each year and if I click not on the table if I click somewhere blank in here it show me the preview of that so this is for 2010 2014 2017 2020 I have all public holidays of all years in one place I don't need the first column anywhere anymore here that's the beauty of using power query because power curries step by step transformation we needed it for previous step we don't need it anymore I'll remove that column and then this holidays column I'll go and expand it I should have had better column names but let's leave it for now I'll expand it click okay now we have public holidays of 2010 all the way to 2020 if I want to clean up this data I can do this clean up here like for example removing these two extra columns calling this column date column changing it to be data type date calling this column public holidays calling this column comments I can do these either here or even in the table three query over there now I have one query that has everything in it and custom functions and parameters are useful for building a scenario that that actually helps you with looping through a number of sources applying same number of transformations and getting the output of that all combined if you like this video go ahead and subscribe to our youtube channel and stay tuned for more power bi and AI videos thank you [Music]

Channel: RADACAD

Views: 22,423

Keywords: Power BI, Power Query, Custom Function, Parameters in Power BI

Id: fGKGDVEtcOU

Length: 16min 44sec (1004 seconds)

Published: Wed May 22 2019