Power Query: Creating a parameterized function in M

This will be part two in my on-going series about Power BI, Power Query, and the general joys of data. In my last post I outlined how to iterate over a list in Power Query. But what if I want to iterate over a list and need to parameterize it with today’s date? What if I want to issue multiple web requests based on a list? This is exactly the scenario I had when trying to fetch crime data from the WA Police web site.

First, I have to thank my colleague and web wizard John Chillemi who authored the azure website that I’m using. The WA Police site has all the data, but not in a nice HTML table, which is what Power Query needs. So, thanks to John, I can execute a single web request and get a webpage that looks like this:

 

The web request takes five parameters – Suburb, Start Month, Start Year, End Month, and End Year, as seen below:

I’d like to execute that web request for every suburb in Western Australia (there are over 1300 by my count) so obviously this is a case for automation, which means I need an M parameterized function.

To create my function, I’m going to let Power Query do most of the work for me. I’ll do my transformations for a single web call, then go back and parameterize it. So first, I’ll create a new query based on a web source, pointing at my web request…

 

and I’ll select the HTML table and choose Edit:

 

I then fix up the data the way I want – use the first row as headers and remove the bottom summary row. Note that I didn’t change any data types – typing of the columns is lost when you execute the function, so there’s no point in doing it here. I also changed the name to fCrimeStats make it clear that this is function code. Once the data is looking good, it’s time to edit the M, which is the code behind all the steps I’ve just done. You can find the code on the View ribbon, in the Advanced Editor. Note that this is the only time that you have to use the Advanced Editor – unless you like to.

 

 

At this point my M looks like this:

let
    Source = Web.Page(Web.Contents("http://crimestatsearch.azurewebsites.net/CrimeStatSearch?suburb=Como&startMonth=1&startYear=1999&endMonth=4&endYear=2015")),
    Data0 = Source{0}[Data],
    #"First Row as Header" = Table.PromoteHeaders(Data0),
    #"Removed Bottom Rows" = Table.RemoveLastN(#"First Row as Header",1),
in
    #"Removed Bottom Rows"

To change it to a function definition, I add the first line (the function definition) and the last two lines, and concatenate in the parameters from the definition into the URL. it looks slightly heinous because the concatenor in M is the ampersand (&), and there are also ampersands in the URL to separate the parameters.

let LoadWebCrimeStats = (suburb as text, startMonth as number, startYear as number, endMonth as number, endYear as number) =>
    let
        Source = Web.Page(Web.Contents("http://crimestatsearch.azurewebsites.net/CrimeStatSearch?suburb="&suburb&"&startMonth="&Text.From(startMonth)&"&startYear="&Text.From(startYear)&"&endMonth="&Text.From(endMonth)&"&endYear="&Text.From(endYear))),
        Data0 = Source{0}[Data],
        #"First Row as Header" = Table.PromoteHeaders(Data0),
        #"Removed Bottom Rows" = Table.RemoveLastN(#"First Row as Header",1)
    in
        #"Removed Bottom Rows"
in
    LoadWebCrimeStats

When I hit OK, I get a new ribbon on my Query Editor menu just for the function, and instead of seeing data, I now see the function definition:

 

You can test your function by hitting the Invoke button, which will pop up a dialog so that you can enter all the parameters. Please note though that you will get an extra step called “Invoked FunctionLoadWebCrimeStats”. Make sure you delete that step, or you no longer have a function that you can call.

 

Now that the function is done, it’s time to apply it to a list of suburbs. Any time you want to iterate over a list in M, the list has to exist inside of Power Query. So I created a new query with data from the DimSuburb table in my local database and then removed the columns that I didn’t want, leaving only the suburb column. The suburbs sometimes have spaces in them, so I created a custom column to URI encode the name:

 

 

For bonus points I added a column for the current month and the current year, so that when I refresh the data, it will always be up to date. DateTime.LocalNow returns the current DateTime based on local settings. I convert it to a date with the DateTime.Date function, because Date.Year expects a date.

 

At this point, I was finally ready to execute the query that I wrote above for each row in this table. So, I added a custom column, and called the function:

 

After my 1300+ web requests were done executing, I expanded the nested table data in the WebData column and removed the URISuburb, CurrentMonth, and CurrentYear columns, leaving me only with the suburb and the crime statistics per month that I was interested it.

 

So, as you can see, you do have to use the Advanced Editor to create a parameterized function in Power Query, but only for the final step that actually creates the function. Otherwise, the process is relatively straight forward.

 

Thanks for reading,

Leah

Categories:

• Technology Updates

Tags:

• Business Intelligence
• Microsoft Power BI
• Power BI
• Power Query