JSON - Expand Multiple Arrays to Columns

[GregLukas]

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

[CNENFRNL · Accepted solution]

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