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": [
        {
            "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

  • 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"