This blog introduces how to create a “custom function” in Power BI, and I am using geocoding in Power BI by connecting API as a practice.
To be honest, at first beginning I think if I want to create a powerful custom function, I have to grasp M language at a certain level first to do this job, because all the samples I’ve seen are with many lines M scripts.
After some practice I realized, actually custom function is not that difficult. (Of course, grasping M language might help us generating more powerful Custom Function)
Custom Function is just like a formula to solve a math problem. E.g. f(x) = x + 2 , you give x a value “1”, the function gives the result “3”, and it’s reusable. The difference is that you might have a more complex problem with multiple steps to solve. But, the concept is the same.
Creating Custom Function in Power BI is you solve a sample of problem first, then replace that sample as a parameter, DONE!
Normally we directly put address fields (country, state, postcode,etc) in to a Power BI Map tile if the data is not geocoded in database, and the issues could be
- Sometimes the locations on the map are not correctly shown up.
- Take very long time if you have thousands rows of address, my guess is the map visual is geocoding the address in background process.
So if we feed Longitude and Latitude to the map visual directly, we will get correct results more efficiently. There are a lot of Geocoding API provider for using, and what we are going do is connecting Geocoding API to retrieve Longitude and Latitude directly in Power BI.
I applied an API Key from MapQuest which provides 15,000 free transactions per month. You could also use Google API or any other provider you like.
And I got the Get URL from Documentation like below
The KEY in this URL needs to be replaced later with my own KEY.
Create Custom Function:
As I said previously, what we need to do first is simply solve one problem by using one sample. In this case it is geocoding one address. Let’s go!
Step 1: This step is to record all the steps of solving problem.
Get data from Web, input the Get URL and replace two parts:
- “KEY” to the API KEY you applied
- “Washington,DC” to the sample we would like use, I use “Leon Capra Dr, Augustine Heights, QLD, 4300” for this case
The result is coming back as JSON file, so we need to transform it to the format you could use, below couples of steps to doing that
- Expand list
- Expand record
- Expand list
- Transform to table
- Expand to columns, select the columns we need, in this case is “latLng”
- Keep expanding columns
- Rename column names
After above transformation, we all know these transformation steps are recorded, and when we open advanced editor, we find the M scripts are coded there. And this is the major part of our custom function.
I put an address without street number on purpose, as Mapquest sometimes returns more than one result. As we only need one result for one address, we need to record transformation steps of filtering one result.
- Add index column
- Filter to get one result only
Till now, the first step is done!
Step 2: This step is to replace the sample with parameter.
Create a parameter
Right click the table we are working on, select “create function”
It should be like this, you will find there is no parameter to use for now.
Open Advanced editor of function, add “Location as text” in bracket and replace the sample “Leon Capra Dr, Augustine Heights, QLD, 4300” with parameter we just created ‘ “&Location” ‘
Congratulations! Your custom function is created.
Consume Custom Function:
Now let’s try the function.
- Go to the table with the address column you would like to geocode, and select “Invoke Custom Function”
- Select the function and the column for location parameter
- Result is coming back, simply expand the columns
- Play with it in the map visual
You see? Looks like we have a lot of steps of creating this custom function. Actually there are only two major steps. Hopefully this blog helps.
Thanks Eric Dong