Parsing complex JSON table

[mjsl5e1]

Hi

First of all, I am fairly strong with excel etc but just getting started with powerQuery/PowerPivot.

The problem, I have a JSON result and I need to make each tree into a seperate table. I can do it with a simpler JSON but I am struggling with this format and I'd greatly appreciate your help.

(Note I have changed the names as the actual data is a bit sensitive but the structure is the same)

{
  "TABLE1": [
    {
      "Field1a": "Value1a",
      "Field1b": "Value1b",
      "Field1c": "Value1c"
    },
    {
      "Field2a": "Value2a",
      "Field2b": "Value2b",
      "Field2c": "Value2c"
    },
    {
      "Field3a": "Value3a",
      "Field3b": "Value3b",
      "Field3c": "Value3c"
    }
  ],
  "TABLE2": [
    {
      "Field1a": "Value1a",
      "Field1b": "Value1b",
      "Field1c": "Value1c"
    },
    {
      "Field2a": "Value2a",
      "Field2b": "Value2b",
      "Field2c": "Value2c"
    }
  ],
  "TABLE3": [
    {
      "Field1a": "Value1a",
      "Field1b": "Value1b"
    }
  ],
  "TABLE4": [
    {
      "Field1a": "Value1a",
      "Field1b": "Value1b",
      "Field1c": "Value1c"
    },
    {
      "Field2a": "Value2a",
      "Field2b": "Value2b",
      "Field2c": "Value2c"
    }
  ],
  "TABLE5": [
    {
      "Field1": "Value1",
      "Field2": Value2
    }
  ]
}

Note that the fields name in EACH TABLE are the same ie Field1a = Field2a = Field3a in TABLE 1. Field1b = Field2b in TABLE 2 and so on.

Now i am struggling on how to manipulate this. What I need is a seperate table for each TABLE. 

For example I need output like this

TABLE1

Colum1 - Colm2  -  Colm3 

Field(a) - Value1a - Value2a

Field(b) - Value1b - Value2b

Field(c) - Value1c - Value2c

Can anyone please guide me? I'm not asking for someone to write the whole code I'd appreciate if you can nudge me in the direction.

I hope I was clear enough. Thank you

[LivioLanzo · Accepted solution]

Hi @mjsl5e1 

I guess there're different ways one can go about this, this PQ script will create a column named 'Tables' containing the tables in the format you asked. Now you have to see if it works on your real data and possible have to tweak it

let
    jsonData  = "{
  ""TABLE1"": [
    {
      ""Field1a"": ""Value1a"",
      ""Field1b"": ""Value1b"",
      ""Field1c"": ""Value1c""
    },
    {
      ""Field2a"": ""Value2a"",
      ""Field2b"": ""Value2b"",
      ""Field2c"": ""Value2c""
    },
    {
      ""Field3a"": ""Value3a"",
      ""Field3b"": ""Value3b"",
      ""Field3c"": ""Value3c""
    }
  ],
  ""TABLE2"": [
    {
      ""Field1a"": ""Value1a"",
      ""Field1b"": ""Value1b"",
      ""Field1c"": ""Value1c""
    },
    {
      ""Field2a"": ""Value2a"",
      ""Field2b"": ""Value2b"",
      ""Field2c"": ""Value2c""
    }
  ],
  ""TABLE3"": [
    {
      ""Field1a"": ""Value1a"",
      ""Field1b"": ""Value1b""
    }
  ],
  ""TABLE4"": [
    {
      ""Field1a"": ""Value1a"",
      ""Field1b"": ""Value1b"",
      ""Field1c"": ""Value1c""
    },
    {
      ""Field2a"": ""Value2a"",
      ""Field2b"": ""Value2b"",
      ""Field2c"": ""Value2c""
    }
  ],
  ""TABLE5"": [
    {
      ""Field1"": ""Value1"",
      ""Field2"": ""Value2""
    }
  ]
}",

json_parsed = Json.Document(jsonData),

ListToTable = Record.ToTable(json_parsed),

ZippedList = Table.AddColumn(
            ListToTable, "ZipList", 
            each 
                  List.Zip(
                    List.Transform([Value], each Record.ToList(_))),
            type list
            ),

  AddedCustom = Table.AddColumn(
                        ZippedList, "Tables", 
                        each let
                              FieldsNumber = List.Max(List.Transform([ZipList], each List.Count(_))),
                              Headers = List.Transform({1..FieldsNumber}, each "Col" & Text.From(_)),
                              Records = List.Transform([ZipList], each Record.FromList(_, Headers)),
                              Tbl = Table.FromRecords(Records)
                            in
                              Tbl,
                        type table)
in
    AddedCustom

[mjsl5e1]

@LivioLanzo 

Can you please help me with the headers? I do have the headers in the list (second column) but the tables dont have any headers.

Also, can you quickly explain how exactly these two lines work (ie what are they doing)

                              Headers = List.Transform({1..FieldsNumber}, each "Col" & Text.From(_)),
                              Records = List.Transform([ZipList], each Record.FromList(_, Headers)),

[mjsl5e1]

Hi

Thanks a lot. It works.

Just one problem it ignores the headers (ie the column 1 is values and not the title "Field1, Field2 etc").

I can now import all the individual tables (ie table within the tables) or I'd need to do something else?Sorry I didnt get a chance to test this i just played around with your script