↑ Return to Power Query

Connection to an API with Power Query

 

With Power Query, you can connect your workbook to an API.

 

What is an API?

API means Application Programming Interface. An API is the interface (website) implemented by an application (the program own by the website) which allows other applications to communicate with it. In our case, the result will be in Excel :)

An API is just an URL with parameters. When you have the good URL and you know parameters expected by the URL, the website returns the information you except.

It’s not clear? Let’s do an easy example.

Connection to a Google API

As an example, we want to calculate the distance between 2 cities. To do that, we need to use to Google Distance Matrix API.

Power_Query_API_1

This page gives a lot of informations for using this API, as:

  • the URL
  • parameters (origins, destination, driving, walking, metrics, imperial, ….)

When you take a look at the URL, you can see at end the word output. Output, is not an information you have to keep, but in the Google API accepts 2 methods for returning a value: XML or JSON. JSON (JavaScript Object Notation), as its name suggests is reserved for the Java application. So, for Excel, we need to use the XML parameter and this choice is obvious because the last x in an Excel xlsx file means that the architecture of the file is based on the XML language.

Write the code in Power Query

To include an API in Power Query, we must write the code in the Script Designer (Power Query > From Other Sources > Blank Query)

Power_Query_API_2

Power Query opens directly on the Script editor and you start to write the following code

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”]])
in
Source

Because we are working with a XML API, we need to include the string in the formula Xml.Document. Then, for parameters, we just have to include them in the second part of the function in a Query field. And the result in Power Query is :

Power_Query_API_3_a


Explore the Xml

In our example we want to return the distance between 2 cities and has you can see in the previous picture, this information is not visible yet. So we need to explore the contain of the API to return only the value between the 2 cities.

In the column Value, click once on the word Table.

Power_Query_API_3

Power Query present now 4 rows in our tables but not yet the result we expect. So we must still drill down to reach the value we want. We click one more time on the word Table on the forth row.

Power_Query_API_4

One more time, Power Query presents a new element of the XML but not the result we expect. So, we click again on the word Table.

Power_Query_API_5

Finally, we reach the data we want but not the value. Let’s click again on the word Table in the row distance.

Power_Query_API_6

Now, we have the result (501 km)  but we must return only value and at this step we have 2 values.

Power_Query_API_7

So, we make a rigth-click on the value we want  (501) and select the option Drill Down

Power_Query_API_8

This is the result we expect. Only value display in the Power Query.

Power_Query_API_9

And the final code generated by Power Query is (menu View > Advanced Editor)

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

And the result in the worksheet is

Power_Query_API_10

So, we have seen how to return the distance between 2 cities with the Google API but just like that it’s not useful to re-use this workbook for 2 other cities. But if we convert this query in a personal function, we can only send as parameters the name of the 2 cities and Power Query will return imediatly the result.

5 comments

Skip to comment form

  1. Eric ADAM

    Bonsoir M. Le Guen,
    J’utilise Excel 2016 MSO (16.0.6326.1010) 64 bits, installé depuis HUP.
    Je souhaite écrire un query, mon premier, et j’ai du mal à faire la relation entre les vues Excel 2013 / Power Query, avec ce que j’ai à l’écran. Après le copier-coller du code “let Source …” je n’arrive pas à la fenêtre avec les colonnes Name, Namespace, Value,…
    Vous serait il possible de m’aider ?
    Merci. Cordialement,
    Eric ADAM

    1. Frédéric LE GUEN

      Bonsoir,
      Si vous pouvez attendre un peu, je suis en train de terminer un livre entièrement sur Power Query dans lequel je reprendrais ce cas intégralement.
      La sortie n’est pas prévu pour tout de suite, je vous l’accorde mais vous y aurez cette fois des captures d’écran en français.
      Cordialement

  2. Razzag Falah

    I tried the example and it gives me an error: Expression.SyntaxError: Invalid identifier.

  3. PRM

    How do I modify the query to pull from cells in an Excel sheet? Or columns in a data model.
    Example, I have about 100 rows of data which list two sets of zip codes in Access and I want to know the distance between them.
    The data updates daily, so manually inputting the zip codes isn’t realistic.

    1. Frédéric LE GUEN

      The problem is use the Google API for a lot of data. Mister Google is not stupid, he agrees to let people use its API for free for few connections. If you want to use the API for a big number of data, the API will return nothing. If you need to calculate the distance between 2 zipcode for your company, you might buy a permanent access to the API

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>