Forum Discussion

GregLukas's avatar
GregLukas
New Member
5 years ago
Solved

JSON - Expand Multiple Arrays to Columns

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

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

1 Reply

  • CNENFRNL's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity Champion

    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"