Forum Discussion
JSON to multiple 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"