BLOG

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

November 12, 2021
   
Solution
Posted by Georges Lagardère

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.

connect to source

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

Convert to Table

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:

Click on the Column button – right side of the single column

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

expand the column to put JSON fields into each column

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

data from web, in excel 🙂

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 Lagardere Sales Director RAW Labs

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


Want to learn more?

Learn More

Blog

Hello world, meet RAW

here we show how to use RAW Data Product Platform to create a simple Hello World output

Blog

Tutorial 1. First flight with RAW

First Tutorial with RAW where we take reader through a first query, first API and passing parameters to that API

Blog

12 Benefits of Data Sharing via APIs

Data Sharing via APIs is a modern approach to unlocking valuable resources inside your organisation. Businesses sharing data are more likely to outperform those who do not. Read more about why using APIs is a better approach.

Blog

How we use RAW internally: a Business Activity API and Dashboard

At RAW we eat our own dog food, as an emerging software vendor we want business metrics on how we are doing. We use RAW to create a Business Activity API, and then retrieve the data in Excel with a simple dashboard.