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.


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 opens directly on the Script editor and you start to write the following code

Source = Xml.Document( Web.Contents(“”, [Query=[origins =”Paris”, destinations =”Amsterdam”, sensor =”false”, mode=”driving”, units = “metric”]])

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 :


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 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.


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.


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


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


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


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


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

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

And the result in the worksheet is


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.


Skip to comment form

  1. 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. 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.

    • Razzag Falah on 17/05/2015 at 00:42
    • Reply

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

    • PRM on 28/08/2014 at 18:49
    • Reply

    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. 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.