Forum Discussion
GregLukas
5 years agoNew Member
JSON - Expand Multiple Arrays to Columns
Hi,
I am struggling to properly import the following JSON data structure into PowerBI Desktop:
{
"items": [
{
"id": "E1",
"stat": [
{
"columnA": [
"E1valA1",
"E1valA2",
"E1valA3",
"E1valA4"
],
"columnB": [
"E1valB1",
"E1valB2",
"E1valB3",
"E1valB4"
]
}
]
},
{
"id": "E2",
"stat": [
{
"columnA": [
"E2valA1",
"E2valA2",
"E2valA3",
"E2valA4"
],
"columnB": [
"E2valB1",
"E2valB2",
"E2valB3",
"E2valB4"
]
}
]
}
]
}
The desired table should look like this:
id columnA columnB
E1 E1valA1 E1valB1
E1 E1valA2 E1valB2
E1 E1valA3 E1valB3
...
E2 E2valA1 E2valB1
E2 E2valA2 E2valB2
E2 E2valA3 E2valB3
The issue is expanding both arrays (columA and columB) - doing it sequentialls, it duplicates values for columnA. This is the query I've got so far:
let
Source = Json.Document(File.Contents("C:\Users\...\Documents\test.json")),
#"Converted to Table" = Table.FromRecords({Source}),
#"Expanded items" = Table.ExpandListColumn(#"Converted to Table", "items"),
#"Expanded items1" = Table.ExpandRecordColumn(#"Expanded items", "items", {"id", "stat"}, {"items.id", "items.stat"}),
#"Expanded items.stat" = Table.ExpandListColumn(#"Expanded items1", "items.stat"),
#"Expanded items.stat1" = Table.ExpandRecordColumn(#"Expanded items.stat", "items.stat", {"columnA", "columnB"}, {"items.stat.columnA", "items.stat.columnB"}),
#"Expanded items.stat.columnA" = Table.ExpandListColumn(#"Expanded items.stat1", "items.stat.columnA"),
#"Expanded items.stat.columnB" = Table.ExpandListColumn(#"Expanded items.stat.columnA", "items.stat.columnB")
in
#"Expanded items.stat.columnB"
Any help or suggestion appreciated.
Thanks,
Gregor
GregLukas , you might want to try,
let Source = Json.Document("{ ""items"": [ { ""id"": ""E1"", ""stat"": [ { ""columnA"": [ ""E1valA1"", ""E1valA2"", ""E1valA3"", ""E1valA4"" ], ""columnB"": [ ""E1valB1"", ""E1valB2"", ""E1valB3"", ""E1valB4"" ] } ] }, { ""id"": ""E2"", ""stat"": [ { ""columnA"": [ ""E2valA1"", ""E2valA2"", ""E2valA3"", ""E2valA4"" ], ""columnB"": [ ""E2valB1"", ""E2valB2"", ""E2valB3"", ""E2valB4"" ] } ] } ] }"), items = Source[items], #"Converted to Table" = Table.FromList(items, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "stat"}, {"id", "stat"}), #"Expanded stat" = Table.ExpandListColumn(#"Expanded Column1", "stat"), //major step of the solution Custom1 = Table.TransformColumns(#"Expanded stat", {"stat", each Table.FromColumns(Record.ToList(_))}), #"Expanded stat1" = Table.ExpandTableColumn(Custom1, "stat", {"Column1", "Column2"}, {"Column1", "Column2"}) in #"Expanded stat1"
1 Reply
- CNENFRNL
Community Champion
GregLukas , you might want to try,
let Source = Json.Document("{ ""items"": [ { ""id"": ""E1"", ""stat"": [ { ""columnA"": [ ""E1valA1"", ""E1valA2"", ""E1valA3"", ""E1valA4"" ], ""columnB"": [ ""E1valB1"", ""E1valB2"", ""E1valB3"", ""E1valB4"" ] } ] }, { ""id"": ""E2"", ""stat"": [ { ""columnA"": [ ""E2valA1"", ""E2valA2"", ""E2valA3"", ""E2valA4"" ], ""columnB"": [ ""E2valB1"", ""E2valB2"", ""E2valB3"", ""E2valB4"" ] } ] } ] }"), items = Source[items], #"Converted to Table" = Table.FromList(items, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "stat"}, {"id", "stat"}), #"Expanded stat" = Table.ExpandListColumn(#"Expanded Column1", "stat"), //major step of the solution Custom1 = Table.TransformColumns(#"Expanded stat", {"stat", each Table.FromColumns(Record.ToList(_))}), #"Expanded stat1" = Table.ExpandTableColumn(Custom1, "stat", {"Column1", "Column2"}, {"Column1", "Column2"}) in #"Expanded stat1"