Click “Close & Load” and load the data into your spreadsheet.

That’s it! You now have the JSON data in a tabular format. Once you do this, it will show all of the columns. We’ll again click the Expand Column button for the third column (id) and 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. To expand the columns, simply click the Expand Column button and then click “OK” to confirm the new columns. Now our data is in a table format, but it still looks the same as before – we can’t see the record details. Because the data is not delimited, we can select “None” as the delimiter and click OK.

But we can’t see the actual data.īut the records are now broken into rows, so click the Convert To Table button.Ī dialogue pops up. When you first import the data into power query, it will look like this.įrom 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.Īt this point, we can see the three records broken into rows – which is what we want. For this example, we’ll load the file by going to the Data tab > Get Data menu > From File > From JSON. 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. 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.įor this tutorial, we’ll use two basic example data sets from. 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.

Exceltojson