9

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?

CountZero
  • 93
  • 1
  • 1
  • 4

1 Answers1

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"
hellow
  • 103
  • 5
Mike Honey
  • 2,512
  • 2
  • 11
  • 12
  • 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 01:37
  • You're welcome. W Gibson fan? – Mike Honey Sep 24 '15 at 03:21
  • Sure am. Was it that transparent? – CountZero Sep 24 '15 at 03:27
  • Only to those who took the red pill ... – Mike Honey Sep 24 '15 at 03: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: https://gist.github.com/Mike-Honey/0a252edf66c3c486b69b – Mike Honey Jun 26 '16 at 23:13
  • I'm trying to do the same but my json is structured like a key-value map, So far i was unable to make the required changes in your answer to make it work for my case http://superuser.com/questions/1164137/loading-a-json-key-value-map-into-power-query – MichaelB Jan 06 '17 at 12:39