8

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?

9

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"
| improve this answer | |
  • Thanks, Mike. Works really well. I'm trying to adapt it to a really complex JSON file I downloaded. I think this will get me 90% of the way there. – CountZero Sep 24 '15 at 1:37
  • You're welcome. W Gibson fan? – Mike Honey Sep 24 '15 at 3:21
  • Sure am. Was it that transparent? – CountZero Sep 24 '15 at 3:27
  • Only to those who took the red pill ... – Mike Honey Sep 24 '15 at 3:34
  • PS: I've since managed to clone Chris Webb's ExpandAll function to one that works on Record-type columns - expanding every column as far as it will go. Check out: gist.github.com/Mike-Honey/0a252edf66c3c486b69b – Mike Honey Jun 26 '16 at 23:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.