Importing JSON data into Excel power query

Working with nested JSON data in Excel

If you’ve ever needed a quick way to do statistical analysis of JSON data or convert it to a delimited format like CSV, Microsoft Excel’s power query gives you that ability.

However, if you’re used to only working with traditional delimited data like CSVs, there will likely be some extra steps to getting your data set up how you want it, particularly if you are working with nested objects within your JSON data.

For this tutorial, we’ll use two basic example data sets from SitePoint.com.

If you want to follow along with the tutorial, you can copy the JSON examples from the Youtube and Google Maps pages into separate Notepad files and save with the .json extension. For this example, we’ll load the file by going to the Data tab > Get Data menu > From File > From JSON.

Expanding columns

We’ll start with the YouTube sample. When you first import the data into power query, it will look like this.

From looking file, we know the data we care about is three records that are nested under “items.” So in order to expand this data, first we’ll click on “List” for the items object.

At this point, we can see the three records broken into rows – which is what we want. But we can’t see the actual data.

But the records are now broken into rows, so click the Convert To Table button.

A dialogue pops up. Because the data is not delimited, we can select “None” as the delimiter and click OK.

Now our data is in a table format, but it still looks the same as before – we can’t see the record details. To expand the columns, simply click the Expand Column button and then click “OK” to confirm the new columns.

We’re now able to start seeing the record data, but the third column has additional nested items underneath it.

We’ll again click the Expand Column button for the third column (id) and click “OK” to confirm the new columns. Once you do this, it will show all of the columns.

That’s it! You now have the JSON data in a tabular format. Click “Close & Load” and load the data into your spreadsheet.

Working with arrays

The previous example was pretty straightforward, but what if your data contains arrays? There is an additional step when you are expanding columns in your query.

For this example, we’ll use the Google Maps example data, which contains coordinate data stored like this:

"location": [25.2285, 55.3273]

When you import the data, you will see the “markers” object. Click “List” to expand.

At this point we see our records broken into rows, which is what we want. So click the Convert To Table button. Confirm there is no delimiter and click “OK”.

You will now see the records as rows in a table. Click the Expand Columns button and OK the new columns.

Because the position and location data are stored as arrays, we’ll need to take a different step to preserve this data. In the position column, click the Expand Column button again, but this time it will give you two options. Select “Extract Values”

Select “Comma” as the delimiter and click OK. It will now look like this:

Repeat the same step for the location column and then click “Close & Load” to load into your spreadsheet.

Alternatively if you wanted the data within the array broken into new rows, you can select “Expand to New Rows” when you click the Expand Column button.