Retrieving JSON data from a REST API in Excel with Power Query

Intro

There’s very many datasets available through APIs now. Using RAW you can create your own APIs easily, but it’s likely that you will want to show users the data in their favourite data analysis tool.

Here we show how to use Power Query in Excel to retrieve data from any REST API, in this case it is some data from Wikipedia, which we have created an API in RAW as an example.

Here’s our API endpoint:

https://api.raw-labs.com/examples/1/public/wikipedia/heads-of-government

This API returns JSON data structure, but others may return XML, or even tabular data in CSV. If you click on the link above you will see the full results, otherwise here’s a snippet:

[
  {
    "countryLabel": "Czech Republic",
    "hgovernmentLabel": "Petr Fiala",
    "twittername": "P_Fiala",
    "article": "https://en.wikipedia.org/wiki/Petr_Fiala"
  },
  {
    "countryLabel": "Slovakia",
    "hgovernmentLabel": "Eduard Heger",
    "twittername": "eduardheger",
    "article": "https://en.wikipedia.org/wiki/Eduard_Heger"
  },
...

Let’s now open Microsoft Excel and use standard functionality in Power Query:

Excel: Get Data… From Web

First, you need to create a new empty spreadsheet in Excel. Then, from the Data menu click “From Web”:

This will open a wizard which asks for a URL, enter the one above (or even something else if you like):

Note: This example is a public API, i.e. with no API Key or token. In the case of a private API endpoint you would need to set the Bearer Authentication token in the HTTP header, or pass in an API key as a parameter in Excel, you can do this with the Advanced option.

If you are asked about accessing public sites, simply click Connect….

Excel: Transform to Columns

Our API above returns list of records, but each will still be in JSON format but in a single column.

Excel allows you to easily convert those fields into columns. From the same Transform Menu, click “Convert > To Table” button :

Finally, at the top right of Column1, click the “Expand Column” button in the corner, this will allow you to choose the columns you want to split out into columns:

The result is the data from the API returned into columns in your spreadsheet:

Now Click “Close and Load“, and you’re done…..

Save the spreadsheet, and next time you open it, just refresh the data using the button on the ribbon.

Wrap Up

I hope you found this useful, and can quickly get going with using API-based data from us at RAW Labs (and indeed any other platform) – from inside the world’s favourite data analysis tool.

To see how we created this API incredibly easily, see the links below.

Georges Lagardère, VP Customer Experience, RAW Labs.

Want to learn more?

• Take a tour of our Demo Catalogue
• Have Questions / Comments? Ask away – join our Community discussion!
• Read our Tutorials and Getting Started Docs
• Why not follow us on LinkedIn, Twitter, YouTube
• Like code? head on over to GitHub
• Developer? Join us! we are looking for bright minds – at all levels of seniority, in databases, distributed systems, UI/UX.