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

When you have created your data API through RAW, 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 the last updated web pages from a website virgingalactic.com as an example.

First, you need to create a new empty spreadsheet in Excel. Then, from the Data menu create a Blank Query ( “Data | Get Data | From Other Sources | Blank Query” )

This will open the Power Query Editor. From there, go to the View tab and open the Advanced Query Editor ( “View | Advance Query Editor” ). There, you can replace the default empty query with your own query, for example this one:

https://api.raw-labs.com/demos/1/public/sitemap/recentpages?website=https://virgingalactic.com

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 paramter in Excel.

Our API above returns list of records within brackets (click …. ), you can expand the columns and get the field names with this button:

From there you can choose which fields you want to load (‘loc’ and ‘lastmod’ in this example)and once done, select “Close & Load” from the File menu. The API endpoint will be contacted and the selected data loaded and the fields displayed in your spreadsheet.

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.

Don’t forget to check out our other blog posts too.

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

Why not follow us on LinkedIn, or Twitter.