Presentation
Let’s take back the example of the connection to a Google API. In this example, we have connect Excel with a Google API to return the distance between 2 cities.
But at this step, it is really no convenient to use the query because each time you want to calculate the distance between 2 new cities, you have to change all the time the parameters in the query.
To avoid this, we will convert the query into a UDF
Create an UDF
Convert a query into an User Defined Function is not very difficult. You just have to embrace your query by the name of your function and also create some parameters. In our example, the code is
Source = Xml.Document(
Web.Contents(“http://maps.googleapis.com/maps/api/distancematrix/xml”,
[Query=[origins =”Paris”, destinations =”Amsterdam”, sensor =”false”, mode=”driving”, units = “metric”]])),
Value = Source{0}[Value],
Value1 = Value{3}[Value],
Value2 = Value1{0}[Value],
Value3 = Value2{2}[Value],
Value4 = Value3{1}[Value]
in
Value4
Now, to create our UDF, we need to add the name of the UDF before the let (here Distance_City) and also write the result of the query, in the name of the UDF
Distance_City=(parameters) =>
let
Source = Xml.Document(
Web.Contents(“http://maps.googleapis.com/maps/api/distancematrix/xml”,
[Query=[origins =”Paris”, destinations =”Amsterdam”, sensor =”false”, mode=”driving”, units = “metric”]])),
Value = Source{0}[Value],
Value1 = Value{3}[Value],
Value2 = Value1{0}[Value],
Value3 = Value2{2}[Value],
Value4 = Value3{1}[Value]
in
Value4
in
Distance_City
Here, the most important is the symbol =>
Without this symbol, Power Query is not able to understand that you want to create an UDF
To finish, we need to add parameters to the UDF. We need, at least, 2 parameters: the start city and the arrival city.
First, we add 2 variables to the name of or UDF
Then, in the API, we change the origin and destination by the variables.
Web.Contents(“http://maps.googleapis.com/maps/api/distancematrix/xml”,
[Query=[origins =Start_City, destinations =Arrival_City, sensor =”false”, mode=”driving”, units = “metric”]]))
The final code is
Distance_City=(Starting_City, Arrival_City) =>
let
Source = Xml.Document(
Web.Contents(“http://maps.googleapis.com/maps/api/distancematrix/xml”,
[Query=[origins =Starting_City, destinations =Arrival_City, sensor =”false”, mode=”driving”, units = “metric”]])),
Value = Source{0}[Value],
Value1 = Value{3}[Value],
Value2 = Value1{0}[Value],
Value3 = Value2{2}[Value],
Value4 = Value3{1}[Value]
in
Value4in
Distance_City
When you close the script editor, you only see the name of your formula with your parameters expected
Call the Power Query UDF
To use this UDF, we need to use again Power Query. But before that, we have to create another table with the city name. So first, you create a document where you write your cities’ name. Then, convert your list into a table (Insert>Table or shortcut Ctrl + T) and rename your table Table_City for instance.
Now we will link this Table with our UDF. To do that, we are going to use Power Query one more time but this time, we will activate the menu From Table to indicate that our source of data comes from the active Table in our spreadsheet.
In Power Query, we are going to insert a new column in our Table
You give to this new column a name (here KM) and in the value of this column, you enter the name of your UDF, which is the name of the previous query). The function expects 2 parameters ( Start city, Arrival city) and that’s exactly the column available.
Press OK and now return to Excel by clicking on Apply & Close
In Excel, you have now 2 tables
- 1 with your parameters (the cities)
- 1 with the result of the UDF.
You have also 2 queries
- 1 for your UDF
- 1 for the integration of your UDF in your initial table
You can download this example here