Forum Discussion

mjsl5e1's avatar
mjsl5e1
New Member
7 years ago
Solved

Parsing complex JSON table

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...
Need Help
Tutorial Requests
  • LivioLanzo's avatar
    LivioLanzo
    7 years ago

    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
LivioLanzo's avatar
LivioLanzo
Icon for Solution Sage rankSolution Sage
7 years ago

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's avatar
    mjsl5e1
    New Member
    7 years ago

    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

     

     

  • mjsl5e1's avatar
    mjsl5e1
    New Member
    7 years ago

    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)),