Forum Discussion
Parsing complex JSON table
Hi
First of all, I am fairly strong with excel etc but just getting started with powerQuery/PowerPivot.
The problem, I have a JSON result and I need to make each tree into a seperate table. I can do it with a simpler JSON but I am struggling with this format and I'd greatly appreciate your help.
(Note I have changed the names as the actual data is a bit sensitive but the structure is the same)
{
"TABLE1": [
{
"Field1a": "Value1a",
"Field1b": "Value1b",
"Field1c": "Value1c"
},
{
"Field2a": "Value2a",
"Field2b": "Value2b",
"Field2c": "Value2c"
},
{
"Field3a": "Value3a",
"Field3b": "Value3b",
"Field3c": "Value3c"
}
],
"TABLE2": [
{
"Field1a": "Value1a",
"Field1b": "Value1b",
"Field1c": "Value1c"
},
{
"Field2a": "Value2a",
"Field2b": "Value2b",
"Field2c": "Value2c"
}
],
"TABLE3": [
{
"Field1a": "Value1a",
"Field1b": "Value1b"
}
],
"TABLE4": [
{
"Field1a": "Value1a",
"Field1b": "Value1b",
"Field1c": "Value1c"
},
{
"Field2a": "Value2a",
"Field2b": "Value2b",
"Field2c": "Value2c"
}
],
"TABLE5": [
{
"Field1": "Value1",
"Field2": Value2
}
]
}Note that the fields name in EACH TABLE are the same ie Field1a = Field2a = Field3a in TABLE 1. Field1b = Field2b in TABLE 2 and so on.
Now i am struggling on how to manipulate this. What I need is a seperate table for each TABLE.
For example I need output like this
TABLE1
Colum1 - Colm2 - Colm3
Field(a) - Value1a - Value2a
Field(b) - Value1b - Value2b
Field(c) - Value1c - Value2c
Can anyone please guide me? I'm not asking for someone to write the whole code I'd appreciate if you can nudge me in the direction.
I hope I was clear enough. Thank you
Hi mjsl5e1
I guess there're different ways one can go about this, this PQ script will create a column named 'Tables' containing the tables in the format you asked. Now you have to see if it works on your real data and possible have to tweak it
let jsonData = "{ ""TABLE1"": [ { ""Field1a"": ""Value1a"", ""Field1b"": ""Value1b"", ""Field1c"": ""Value1c"" }, { ""Field2a"": ""Value2a"", ""Field2b"": ""Value2b"", ""Field2c"": ""Value2c"" }, { ""Field3a"": ""Value3a"", ""Field3b"": ""Value3b"", ""Field3c"": ""Value3c"" } ], ""TABLE2"": [ { ""Field1a"": ""Value1a"", ""Field1b"": ""Value1b"", ""Field1c"": ""Value1c"" }, { ""Field2a"": ""Value2a"", ""Field2b"": ""Value2b"", ""Field2c"": ""Value2c"" } ], ""TABLE3"": [ { ""Field1a"": ""Value1a"", ""Field1b"": ""Value1b"" } ], ""TABLE4"": [ { ""Field1a"": ""Value1a"", ""Field1b"": ""Value1b"", ""Field1c"": ""Value1c"" }, { ""Field2a"": ""Value2a"", ""Field2b"": ""Value2b"", ""Field2c"": ""Value2c"" } ], ""TABLE5"": [ { ""Field1"": ""Value1"", ""Field2"": ""Value2"" } ] }", json_parsed = Json.Document(jsonData), ListToTable = Record.ToTable(json_parsed), ZippedList = Table.AddColumn( ListToTable, "ZipList", each List.Zip( List.Transform([Value], each Record.ToList(_))), type list ), AddedCustom = Table.AddColumn( ZippedList, "Tables", each let FieldsNumber = List.Max(List.Transform([ZipList], each List.Count(_))), Headers = List.Transform({1..FieldsNumber}, each "Col" & Text.From(_)), Records = List.Transform([ZipList], each Record.FromList(_, Headers)), Tbl = Table.FromRecords(Records) in Tbl, type table) in AddedCustom
3 Replies
Hi mjsl5e1
I guess there're different ways one can go about this, this PQ script will create a column named 'Tables' containing the tables in the format you asked. Now you have to see if it works on your real data and possible have to tweak it
let jsonData = "{ ""TABLE1"": [ { ""Field1a"": ""Value1a"", ""Field1b"": ""Value1b"", ""Field1c"": ""Value1c"" }, { ""Field2a"": ""Value2a"", ""Field2b"": ""Value2b"", ""Field2c"": ""Value2c"" }, { ""Field3a"": ""Value3a"", ""Field3b"": ""Value3b"", ""Field3c"": ""Value3c"" } ], ""TABLE2"": [ { ""Field1a"": ""Value1a"", ""Field1b"": ""Value1b"", ""Field1c"": ""Value1c"" }, { ""Field2a"": ""Value2a"", ""Field2b"": ""Value2b"", ""Field2c"": ""Value2c"" } ], ""TABLE3"": [ { ""Field1a"": ""Value1a"", ""Field1b"": ""Value1b"" } ], ""TABLE4"": [ { ""Field1a"": ""Value1a"", ""Field1b"": ""Value1b"", ""Field1c"": ""Value1c"" }, { ""Field2a"": ""Value2a"", ""Field2b"": ""Value2b"", ""Field2c"": ""Value2c"" } ], ""TABLE5"": [ { ""Field1"": ""Value1"", ""Field2"": ""Value2"" } ] }", json_parsed = Json.Document(jsonData), ListToTable = Record.ToTable(json_parsed), ZippedList = Table.AddColumn( ListToTable, "ZipList", each List.Zip( List.Transform([Value], each Record.ToList(_))), type list ), AddedCustom = Table.AddColumn( ZippedList, "Tables", each let FieldsNumber = List.Max(List.Transform([ZipList], each List.Count(_))), Headers = List.Transform({1..FieldsNumber}, each "Col" & Text.From(_)), Records = List.Transform([ZipList], each Record.FromList(_, Headers)), Tbl = Table.FromRecords(Records) in Tbl, type table) in AddedCustomHi
Thanks a lot. It works.
Just one problem it ignores the headers (ie the column 1 is values and not the title "Field1, Field2 etc").
I can now import all the individual tables (ie table within the tables) or I'd need to do something else?Sorry I didnt get a chance to test this i just played around with your script
Can you please help me with the headers? I do have the headers in the list (second column) but the tables dont have any headers.
Also, can you quickly explain how exactly these two lines work (ie what are they doing)
Headers = List.Transform({1..FieldsNumber}, each "Col" & Text.From(_)), Records = List.Transform([ZipList], each Record.FromList(_, Headers)),
