Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to Solution.
@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"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@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"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.