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

Reference Data Standard Library
Standard Libraries are a commonly known construct in programming. Same for data; we show how to use RAW to surface and standardise data into APIs

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

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