JSON to multiple tables

[ramon5023]

Hi, I have the following JSON which I would like to convert to two tables, like so:

{"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}]}

Table: t1
Cols: c1, c2

a,  1

b,  2

c,  3

(table has three rows, column cc1 has values a, b, c)

Table: t2

Cols: cc1, cc2, cc3

d,  4,  44

e,  5,  55

f,  6,  66

I read the JSON, parse it through Transform->Parse->JSON and get the following. Not sure what to do next. 

t1, List 

t2, List

TIA

[ramon5023]

This "solution" has a major problem because it forces one to ingest a potentially large JSON dataset twice, and then throws away the results because DAX isn't able to output two tables. What if you have to process 3 tables, or 5?! This is really a poor workaround, at best, and I am surprised no one else has stepped up to offer anything better ... This is in addition to having to manually type in the whole schema, instead of just parsing the JSON and using the schema that's encapsulated in the JSON.

[ramon5023]

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?

[ChiragGarg2512]

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)

[ramon5023]

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"

[ChiragGarg2512]

There is a problem with the JSON data as there is an extra ',' in the t2 section.

{"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}]}.

Also you need to expand the list.

If anything doesn't work use this in blank query:

let
Source = Json.Document(File.Contents(File Location)),
#"Converted to Table" = Table.FromRecords({Source}),
#"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"}),
Custom1 = #"Expanded t4"
in
Custom1

**Replace the File Location in query to the location of JSON file in the local machine.

[ChiragGarg2512]

@ramon5023 , It doesn't create two different tables as the JSON has been uploaded to a single query. What can be done is the creation of a duplicate table. Now for the two tables that are in possesion, one of the columns can be deleted(t1 in one and t2 in the other). This would result in two different tables, one with only t1 and other with only t2. Expand them after the deletion is done and what is left are two tables.

What is happening is that power BI uploads a JSON file and that file contains two objects. These objects get their own columns where they can be put in a table. 

For t1:

let
    Source = Json.Document(File.Contents(FileLocation)),
    #"Converted to Table" = Table.FromRecords({Source}),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"t1", type any}, {"t2", type any}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"t2"}),
    #"Expanded t1" = Table.ExpandListColumn(#"Removed Columns", "t1"),
    #"Expanded t2" = Table.ExpandRecordColumn(#"Expanded t1", "t1", {"c1", "c2"}, {"t1.c1", "t1.c2"})
in
    #"Expanded t2"

For t2:

let
    Source = Json.Document(File.Contents("C:\Users\USER\OneDrive\Desktop\data.json")),
    #"Converted to Table" = Table.FromRecords({Source}),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"t1", type any}, {"t2", type any}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"t1"}),
    #"Expanded t2" = Table.ExpandListColumn(#"Removed Columns", "t2"),
    #"Expanded t1" = Table.ExpandRecordColumn(#"Expanded t2", "t2", {"cc1", "cc2", "cc3"}, {"t2.cc1", "t2.cc2", "t2.cc3"})
in
    #"Expanded t1"