Excel Power Query - Load JSON as a table

by GarciaPL on Sunday 18 February 2018

If you would like to load JSON and present it as a table, it's quite easy if you have an Excel 2016 [1]. It's more complicated, once you have a lower version.

First of all, try to install an additional plugin called Power Query [2].

In the Power Query ribbon tab, click From Other Sources > Blank Query, then go to Advanced Editor and input below query string. Do not forget to change the path to your JSON.

Click -> Close & Load

let

Source = Json.Document(File.Contents("Z:\Directory\input.json")),
AsTable = Table.FromRecords(Source)

in

AsTable

Click on 'Close & Load'
Reference :
[1] Microsoft Support - Connect to a JSON file
[2] Power Query for Excel