Import JSON Data in Excel 2016 or 2019 or Office 365 using a Get & Transform Query

If you have data stored in JSON format that you would like to import into Excel, it’s now very easy and doesn’t require any VBA to import data locally from the disk or from a web API. Watch the video or read the steps below!

Step 1: Open The Data in the Query Editor

When clicking “From JSON”, you will be presented with a file browser. Find the file on your disk and click Import. This should bring you into the query editor. If you are using a web API, you may choose instead to import the data directly from the web. To do this, instead of clicking “From JSON”, click “From Web” and enter the URL. This should fetch the file and bring you into the query editor.

Step 2: Craft the Query

Start by finding the list in the data. Click through any single records until you find a list. Click it to open the list of records.

One you have found the list you are looking for, the list needs to be converted to a table for Excel to use. Click “To Table” to convert it.

Once you have the list as a table, you need to specify which columns you want excel to make use of as well as have the opportunity to specify how any inner lists (array fields) may be dealt with. Click the icon with two arrows pointing away from one another to expand the table. You may want to rename the table before doing this. To do so, double click the name and type a new one.

Step 3: Bring the Table Back Into Excel

Click Close and Load to bring the data back into Excel as a Table. A new sheet may be created.

Enjoy the finished product!