↑ Return to Power Query

UDF

 

With Power Query, you can easily convert a query in an UDF.

 

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

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

 

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

let
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

Distance_City=(Start_City, Arrival_City)

Then, in the API, we change the origin and destination by the variables.

Source = Xml.Document(
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

let
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

Power_Query_UDF_1

 

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.

Power_Query_UDF_2

 

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.

Power_Query_UDF_3


In Power Query, we are going to insert a new column in our Table

Power_Query_UDF_4

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.

Power_Query_UDF_5

Press OK and now return to Excel by clicking on Apply & Close

Power_Query_UDF_6

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

Power_Query_UDF_7

You can download this example here

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>