Forum Discussion
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": [
{
"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"
}
}
}
]
}
My problem is the field Items.XXXXX with XXXX having a different value everytime, and will have additional values everytime I refresh.
Ideally I want to expand the list Data into this following format in my table :
RowIndex VisitId Score Notes Items Items.name Items.status Items.value
1 105X1 82,89 12563 SB A No
1 105X1 82,89 12566 BC A No
.....
I tried to apply the following code "Custom1" to my Power Query but it doesn't work because of the field with a different value everytime :
let
Source = Json.Document(File.Contents("XXX")),
#"Converted to Table" = Table.FromRecords({Source}),
#"Expanded data" = Table.ExpandListColumn(#"Converted to Table", "data"),
#"Expanded data1" = Table.ExpandRecordColumn(#"Expanded data", "data", {"RowIndex", "VisitId", "Score", "Notes", "Items"}, {"data.RowIndex", "data.VisitId", "data.Score", "data.Notes", "data.Items"})
Custom1 = Table.TransformColumns(#"Expanded data1", {"???????", each Table.FromColumns(Record.ToList(_))}),
Any help would be appreciated.
Thank you
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"
1 Reply
- lbendlin
Super User
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"