Convert a Power BI Query to a Function – Part 2

This topic was inspired by one of the examples in my session: Prep Your Data for Analytics with R & Power BI.

The first post reviewed how to build a query to transform a data set and prepare it for analytics and visualization. This second post will cover how to convert this query into a function that can be applied on multiple data sets of the same structure.

The Power BI report used for these posts can be found here.

Picking up from part one, we had created a query that prepared a data set with information about urban forward sortation areas in British Columbia.

I want to re-use the logic in this query to transform multiple data sets of the same structure. Each data set will relate to the urban FSAs for each province across Canada.

I can convert the query to a function so it can then be applied to the corresponding data sets for each province. I do not have to rebuild the query for each one!

That means that I needed to create a query that lists all of the provinces I want to apply the transformation steps to.

Fortunately, this information is on a Wikipedia page. I connected to the page and navigated to the table containing the information. This was a wide table with only one row of data.

In order to apply my query to each element of the table, I needed the provinces to be in rows instead of columns. I transformed the table by using the =Table.Transpose() function.

This provided the raw list of data that I needed. I did some additional filtering to return the ten rows, one for each of the provinces in Canada.

Before the query was converted to a function, I needed to add a parameter. This is what will allow the logic to be re-used across multiple data sets. The parameter value can change, making the query dynamic.

In this case, the parameter needed to be the letter contained in the second column from my province list. This is the first letter of the FSA in each province.

I created the parameter: Letter. I set the current value to “V”.

The parameter needed to be inserted into the transformation query.

I did this by going back to the source step of the query. This loaded the contents of the Wikipedia page. The URL to the page was defined in the connection string.

This is where I waned to insert the parameter, because most of the connection string was consistent for each of the provinces. The only part that changes is the last letter. I changed the URL by deleting the “V” at the end and replaced it with the Letter parameter.

This doesn’t change or break the query, as the parameter’s current value is “V”. Now the query can be converted to a function!

I right-clicked on the query and selected “Create Function…”. I named the function fnTransformUrbanFSA, which had one parameter: Letter.

This function could be applied to transform the FSAs in each of the provinces! I went back to the provinces query and added a new column using the “Invoke Custom Function” button. This created a table of data containing the transformed FSA information for each province listed.

I expanded the complex column, which resulted in multiple rows of FSA information for each province.

There was some quick clean-up left. I changed the data types of the expanded columns. I also created a new Location column, which I defined with the following formula:

= [City] & ", " & [Province] & ", Canada"

This would prove to be helpful when creating a map visualization in the report.

The data was now ready for the report! A link to the report can be found here.