Forum Discussion

pr92's avatar
pr92
Frequent Visitor
1 year ago
Solved

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": [ { ...
  • lbendlin's avatar
    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"