Loading a json file into Power Query

I am trying to load a JSON file I pulled from data.gov into Power Query for manipulation, but I can't figure out how to get Power Query to convert the JSON file to a tabular format.

Is there a way to convert JSON to a table in Power Query without writing a custom query?

Solution 1:

JSON data often appears as Records within Lists within Records (and other variations). You can usually use the PQ UI to expand those objects without writing code.

Here's a sample JSON endpoint: http://api.nobelprize.org/v1/laureate.json

After running the PQ "From Web", the next step is to click the Record Tools / Convert / Into Table button in the PQ ribbon.

This returns a single row, with the second column (Value) containing a List. Click the expand button next to "Value" to expand the List.

This returns multiple rows, with each second column (Value) now containing a Record. Expand the record to get the columns of data in a tabular format.

Note there is more depth to this particular JSON layout - see the Value.prizes column at the far right. Your exact steps may vary, depending on your JSON input and your output requirements.

Here's the full code to get to a table - each step was generated by clicking on the UI.

let
    Source = Json.Document(Web.Contents("http://api.nobelprize.org/v1/laureate.json")),
    #"Converted to Table" = Record.ToTable(Source),
    #"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"),
    #"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", 
        {"id", "firstname", "surname", "born", "died", "bornCountry", "bornCountryCode", 
         "bornCity", "diedCountry", "diedCountryCode", "diedCity", "gender", "prizes"},
        {"id", "firstname", "surname", "born", "died", "bornCountry", "bornCountryCode",
         "bornCity", "diedCountry", "diedCountryCode", "diedCity", "gender", "prizes"})
in
    #"Expanded Value1"