Forum Discussion
pr92
1 year agoFrequent Visitor
Parse JSON items with different item names into table rows
Hello, I have the following JSON as a source : {
"statusCode": "OK",
"statusMessage": "OK",
"rowCount": 28,
"totalRowCount": 28,
"data": [
{
...
- 1 year ago
let Source = Json.Document("{ ""statusCode"": ""OK"", ""statusMessage"": ""OK"", ""rowCount"": 28, ""totalRowCount"": 28, ""data"": [ { ""RowIndex"": ""1"", ""VisitId"": ""105X1"", ""Score"": ""82,89"", ""Notes"": """", ""Items"": { ""12563"": { ""name"": ""SB"", ""status"": ""A"", ""value"": ""No"" }, ""12566"": { ""name"": ""BC"", ""status"": ""A"", ""value"": ""No"" }, ""12568"": { ""name"": ""BV"", ""status"": ""A"", ""value"": ""No"" } } }, { ""RowIndex"": ""2"", ""VisitId"": ""105Y17"", ""Score"": ""90,07"", ""Notes"": """", ""Items"": { ""8002"": { ""name"": ""TR"", ""status"": ""A"", ""value"": ""Oui"" }, ""8091"": { ""name"": ""LH"", ""status"": ""A"", ""value"": ""Oui"" } } } ] }"), data = Source[data], #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"RowIndex", "VisitId", "Score", "Notes", "Items"}, {"RowIndex", "VisitId", "Score", "Notes", "Items"}), #"Replaced Value" = Table.ReplaceValue(#"Expanded Column1",each [Items],each Record.ToList([Items]),Replacer.ReplaceValue,{"Items"}), #"Expanded Items" = Table.ExpandListColumn(#"Replaced Value", "Items"), #"Expanded Items1" = Table.ExpandRecordColumn(#"Expanded Items", "Items", {"name", "status", "value"}, {"Items.name", "Items.status", "Items.value"}) in #"Expanded Items1"
lbendlin
Super User
1 year agolet
Source = Json.Document("{
""statusCode"": ""OK"",
""statusMessage"": ""OK"",
""rowCount"": 28,
""totalRowCount"": 28,
""data"": [
{
""RowIndex"": ""1"",
""VisitId"": ""105X1"",
""Score"": ""82,89"",
""Notes"": """",
""Items"": {
""12563"": {
""name"": ""SB"",
""status"": ""A"",
""value"": ""No""
},
""12566"": {
""name"": ""BC"",
""status"": ""A"",
""value"": ""No""
},
""12568"": {
""name"": ""BV"",
""status"": ""A"",
""value"": ""No""
}
}
},
{
""RowIndex"": ""2"",
""VisitId"": ""105Y17"",
""Score"": ""90,07"",
""Notes"": """",
""Items"": {
""8002"": {
""name"": ""TR"",
""status"": ""A"",
""value"": ""Oui""
},
""8091"": {
""name"": ""LH"",
""status"": ""A"",
""value"": ""Oui""
}
}
}
]
}"),
data = Source[data],
#"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"RowIndex", "VisitId", "Score", "Notes", "Items"}, {"RowIndex", "VisitId", "Score", "Notes", "Items"}),
#"Replaced Value" = Table.ReplaceValue(#"Expanded Column1",each [Items],each Record.ToList([Items]),Replacer.ReplaceValue,{"Items"}),
#"Expanded Items" = Table.ExpandListColumn(#"Replaced Value", "Items"),
#"Expanded Items1" = Table.ExpandRecordColumn(#"Expanded Items", "Items", {"name", "status", "value"}, {"Items.name", "Items.status", "Items.value"})
in
#"Expanded Items1"