Forum Discussion
JSON to multiple tables
Thanks, but I am not quite sure what to do here. What do you mean by "If anything doesn't work" ? Are you saying the PwoerQuery UI can't do it and I need to insert this code somewhere? Do I need to manually type in the whole schema or is there an automated way to parse the underlying JSON and create the separate tables?
First of all try correct the JSON data as there is an extra ',' which makes the JSON data faulty.
After uploading the file, there will be a need to expand the data(icon present next to the names of the column).
Then use powerQuery to separate the two tables.
If you are still unable to work with the JSON file, go to new source in Power Query Editor and create a blank query.
Then go to advanced editor for the blank query and paste the code/Mquery in the space.(Remember to put the file location on local system in double quotes)
This is the JSON, sans the extra comma -
{"t1":[{"c1":"a","c2":1},{"c1":"b","c2":2},{"c1":"c","c2":3}],
"t2":[{"cc1":"d","cc2":4,"cc3":44},{"cc1":"e","cc2":5,"cc3":55},{"cc1":"f","cc2":6,"cc3":66}]}
Unfortunately, this code doesn't produce two tables, not sure what to do with this output.
let
Source = Json.Document(Web.Contents("http://localhost/", [Timeout=#duration(0, 0, 10, 0)])),
#"Parsed JSON" = Json.Document(Source),
#"Converted to Table" = Table.FromRecords({#"Parsed JSON"}),
#"Expanded t1" = Table.ExpandListColumn(#"Converted to Table", "t1"),
#"Expanded t2" = Table.ExpandRecordColumn(#"Expanded t1", "t1", {"c1", "c2"}, {"t1.c1", "t1.c2"}),
#"Expanded t3" = Table.ExpandListColumn(#"Expanded t2", "t2"),
#"Expanded t4" = Table.ExpandRecordColumn(#"Expanded t3", "t2", {"cc1", "cc2", "cc3"}, {"t2.cc1", "t2.cc2", "t2.cc3"})
in
#"Expanded t4"