Flattening a tree structured json data into stacked tables in Power Query M

[prashkarl]

Very new to power query in general, and I'm having a tough time wrapping my head around this task.

I've got the following json file (queried from an api) where the data is 'tree' structured. The elements of children may or may not have data . The intention is to flatten the data into a table that has the following columns - ID, NAME, PARENT_ID so that it can be used as a lookup table elsewhere.

I know some sort of recurrsive function is involved, but I'm having a hard time forming that logic. Pwease help!

{
"areas":[
{
"id":"949133621471986838",
"name":"ProjectName",
"children":[
{
"id":"949133621472951108",
"name":"01Structure",
"children":[
{
"id":"949133621472951109",
"name":"01Piling",
"children":[
{
"id":"949133621472951111",
"name":"Building",
"children":[]
},
{
"id":"949133621472951110",
"name":"RetainingWall",
"children":[]
}
]
},
{
"id":"949133621472951112",
"name":"02PublicDrainage",
"children":[]
},
{
"id":"949133621472951113",
"name":"GroundFloor",
"children":[
{
"id":"949133621472951122",
"name":"OutsideFootprint",
"children":[]
},
{
"id":"949133621472951114",
"name":"Zone1-NorthEastBuildingB",
"children":[]
},
{
"id":"949133621472951115",
"name":"Zone2-NorthWestBuildingB",
"children":[]
},
{
"id":"949133621472951116",
"name":"Zone3-Center",
"children":[]
},
{
"id":"949133621472951117",
"name":"Zone4-SouthEastBuildingA",
"children":[]
},
{
"id":"949133621472951119",
"name":"Zone5-SouthWestBuildingA",
"children":[]
}
]
},
{
"id":"949133621472951123",
"name":"Level01(Podium)",
"children":[
{
"id":"949133621472951124",
"name":"BuildingA",
"children":[]
},
{
"id":"949133621472951125",
"name":"BuildingB",
"children":[]
},
{
"id":"949133621472951126",
"name":"CentralPodium",
"children":[]
}
]
},
{
"id":"949133621472951127",
"name":"Level02",
"children":[
{
"id":"949133621472951128",
"name":"BuildingA",
"children":[]
},
{
"id":"949133621472951129",
"name":"BuildingB",
"children":[]
}
]
},
{
"id":"949133621472951130",
"name":"Level03",
"children":[
{
"id":"949133621472951131",
"name":"BuildingA",
"children":[]
},
{
"id":"949133621472951132",
"name":"BuildingB",
"children":[]
}
]
},
{
"id":"949133621472951133",
"name":"Level04",
"children":[
{
"id":"949133621472951134",
"name":"BuildingA",
"children":[]
},
{
"id":"949133621472951135",
"name":"BuildingB",
"children":[]
}
]
},
{
"id":"949133621472951136",
"name":"Roof",
"children":[
{
"id":"949133621472951137",
"name":"BuildingA",
"children":[]
},
{
"id":"949133621472951138",
"name":"BuildingB",
"children":[]
}
]
}
]
},
{
"id":"949133621472951139",
"name":"02Facade",
"children":[
{
"id":"949133621473513959",
"name":"BuildingA",
"children":[
{
"id":"949133621473513951",
"name":"EastElevation",
"children":[
{
"id":"949133621473513979",
"name":"GroundFloor",
"children":[]
},
{
"id":"949133621473513980",
"name":"Level1(Podium)",
"children":[]
},
{
"id":"949133621473513981",
"name":"Level2",
"children":[]
},
{
"id":"949133621473513982",
"name":"Level3",
"children":[]
},
{
"id":"949133621473513983",
"name":"Level4",
"children":[]
}
]
},
{
"id":"949133621473513973",
"name":"NorthElevation",
"children":[
{
"id":"949133621473513966",
"name":"GroundFloor",
"children":[]
},
{
"id":"949133621473513967",
"name":"Level1(Podium)",
"children":[]
},
{
"id":"949133621473513968",
"name":"Level2",
"children":[]
},
{
"id":"949133621473513969",
"name":"Level3",
"children":[]
},
{
"id":"949133621473513970",
"name":"Level4",
"children":[]
}
]
},
{
"id":"949133621473513974",
"name":"SouthElevation",
"children":[
{
"id":"949133621473513984",
"name":"GroundFloor",
"children":[]
},
{
"id":"949133621473513985",
"name":"Level1(Podium)",
"children":[]
},
{
"id":"949133621473513986",
"name":"Level2",
"children":[]
},
{
"id":"949133621473513987",
"name":"Level3",
"children":[]
},
{
"id":"949133621473513988",
"name":"Level4",
"children":[]
}
]
},
{
"id":"949133621473513972",
"name":"WestElevation",
"children":[
{
"id":"949133621473513961",
"name":"GroundFloor",
"children":[]
},
{
"id":"949133621473513962",
"name":"Level1(Podium)",
"children":[]
},
{
"id":"949133621473513963",
"name":"Level2",
"children":[]
},
{
"id":"949133621473513964",
"name":"Level3",
"children":[]
},
{
"id":"949133621473513965",
"name":"Level4",
"children":[]
}
]
}
]
},
{
"id":"949133621473513960",
"name":"BuildingB",
"children":[
{
"id":"949133621473513975",
"name":"EastElevation",
"children":[
{
"id":"949133621473513971",
"name":"GroundFloor",
"children":[]
},
{
"id":"949133621473513992",
"name":"Level1(Podium)",
"children":[]
},
{
"id":"949133621473513993",
"name":"Level2",
"children":[]
},
{
"id":"949133621473513994",
"name":"Level3",
"children":[]
},
{
"id":"949133621473513995",
"name":"Level4",
"children":[]
}
]
},
{
"id":"949133621473513977",
"name":"NorthElevation",
"children":[
{
"id":"949133621473514001",
"name":"GroundFloor",
"children":[]
},
{
"id":"949133621473514002",
"name":"Level1(Podium)",
"children":[]
},
{
"id":"949133621473514003",
"name":"Level2",
"children":[]
},
{
"id":"949133621473514004",
"name":"Level3",
"children":[]
},
{
"id":"949133621473514005",
"name":"Level4",
"children":[]
}
]
},
{
"id":"949133621473513978",
"name":"SouthElevation",
"children":[
{
"id":"949133621473513989",
"name":"GroundFloor",
"children":[]
},
{
"id":"949133621473513990",
"name":"Level1(Podium)",
"children":[]
},
{
"id":"949133621473513991",
"name":"Level2",
"children":[]
},
{
"id":"949133621473514012",
"name":"Level3",
"children":[]
},
{
"id":"949133621473514013",
"name":"Level4",
"children":[]
}
]
},
{
"id":"949133621473513976",
"name":"WestElevation",
"children":[
{
"id":"949133621473513996",
"name":"GroundFloor",
"children":[]
},
{
"id":"949133621473513997",
"name":"Level1(Podium)",
"children":[]
},
{
"id":"949133621473513998",
"name":"Level2",
"children":[]
},
{
"id":"949133621473513999",
"name":"Level3",
"children":[]
},
{
"id":"949133621473514000",
"name":"Level4",
"children":[]
}
]
}
]
}
]
}
]
}
]
}

[prashkarl]

Thanks @lbendlin , sorry, I know as much as to do the above, but I'm trying aiming for it to be...

- Dynamic, meaning if hierarchies are added/removed, the query should not fail.
- A final table at the end with only 3 columns ID, NAME and PARENT_ID

[lbendlin]

ok, so you really need a recursive function.  I'm a bit rusty with these so this may take a while.

[lbendlin · Accepted solution]

@prashkarl It took a while to get this right but here it is in all its glory

Note that the hierarchy is not clean, for example "Building A" appears in multiple branches.  So you cannot apply Path functions unless you combine name and ID fields. If you would do that you could do things like 

Thank you for the challenge, this was quite tough to comprehend.

[prashkarl]

Great work! Very elegant given the complexity. Not sure what you mean by Path functions, I'm only going to use this as a lookup table for better definition.

I came up with a recurring as well that creates a list of records as opposed to a table and added some extra juice to the data.

Below is the table which uses the recursive RecursiveFromRecord function

let
    Source = Json.Document(Web.Contents("https://au1.aconex.com/field-management/api/projects/" & AconexProjectID & "/areas", [Headers=[Accept="*/*"]]))[areas],
    Custom1 = RecursiveFromRecord(Source{0},"","",""),
    #"Converted to Table" = Table.FromRecords(Custom1),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"id", Int64.Type}, {"parent_id", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"children"})
in
    #"Removed Columns"

 
And the recursive RecursiveFromRecord is

(rec as record, parentname as text, parentid as text, parent_fullpath as text) =>
let
   ThisRecWithFieldsAdded = 
      Record.AddField(   
         Record.AddField(
            Record.AddField(rec,"parent_id", parentid),
            "fullpath", 
            if parent_fullpath = "" then Record.Field(rec,"name") else parent_fullpath & " > " & Record.Field(rec,"name")
         ), 
         "parent_name", parentname
      ),

   output =
   if List.Count(Record.Field(ThisRecWithFieldsAdded,"children")) = 0 then
      List.Buffer({ThisRecWithFieldsAdded})
   else 
      List.Accumulate(
         Record.Field(ThisRecWithFieldsAdded,"children"),
         List.Buffer({ThisRecWithFieldsAdded}),
         (s_list,c_record) => List.Combine({s_list, @RecursiveFromRecord(c_record,Record.Field(ThisRecWithFieldsAdded,"name"),Record.Field(ThisRecWithFieldsAdded,"id"),Record.Field(ThisRecWithFieldsAdded,"fullpath"))}) 
      )  
in
    output

Output looks something like this...

Much appreciate your help.

[lbendlin]

I mean, it's not pretty but it works.

let
    Source = Json.Document("
    {
  ""areas"": [
    {
      ""id"": ""949133621471986838"",
      ""name"": ""ProjectName"",
      ""children"": [
        {
          ""id"": ""949133621472951108"",
          ""name"": ""01Structure"",
          ""children"": [
            {
              ""id"": ""949133621472951109"",
              ""name"": ""01Piling"",
              ""children"": [
                {
                  ""id"": ""949133621472951111"",
                  ""name"": ""Building"",
                  ""children"": []
                },
                {
                  ""id"": ""949133621472951110"",
                  ""name"": ""RetainingWall"",
                  ""children"": []
                }
              ]
            },
            {
              ""id"": ""949133621472951112"",
              ""name"": ""02PublicDrainage"",
              ""children"": []
            },
            {
              ""id"": ""949133621472951113"",
              ""name"": ""GroundFloor"",
              ""children"": [
                {
                  ""id"": ""949133621472951122"",
                  ""name"": ""OutsideFootprint"",
                  ""children"": []
                },
                {
                  ""id"": ""949133621472951114"",
                  ""name"": ""Zone1-NorthEastBuildingB"",
                  ""children"": []
                },
                {
                  ""id"": ""949133621472951115"",
                  ""name"": ""Zone2-NorthWestBuildingB"",
                  ""children"": []
                },
                {
                  ""id"": ""949133621472951116"",
                  ""name"": ""Zone3-Center"",
                  ""children"": []
                },
                {
                  ""id"": ""949133621472951117"",
                  ""name"": ""Zone4-SouthEastBuildingA"",
                  ""children"": []
                },
                {
                  ""id"": ""949133621472951119"",
                  ""name"": ""Zone5-SouthWestBuildingA"",
                  ""children"": []
                }
              ]
            },
            {
              ""id"": ""949133621472951123"",
              ""name"": ""Level01(Podium)"",
              ""children"": [
                {
                  ""id"": ""949133621472951124"",
                  ""name"": ""BuildingA"",
                  ""children"": []
                },
                {
                  ""id"": ""949133621472951125"",
                  ""name"": ""BuildingB"",
                  ""children"": []
                },
                {
                  ""id"": ""949133621472951126"",
                  ""name"": ""CentralPodium"",
                  ""children"": []
                }
              ]
            },
            {
              ""id"": ""949133621472951127"",
              ""name"": ""Level02"",
              ""children"": [
                {
                  ""id"": ""949133621472951128"",
                  ""name"": ""BuildingA"",
                  ""children"": []
                },
                {
                  ""id"": ""949133621472951129"",
                  ""name"": ""BuildingB"",
                  ""children"": []
                }
              ]
            },
            {
              ""id"": ""949133621472951130"",
              ""name"": ""Level03"",
              ""children"": [
                {
                  ""id"": ""949133621472951131"",
                  ""name"": ""BuildingA"",
                  ""children"": []
                },
                {
                  ""id"": ""949133621472951132"",
                  ""name"": ""BuildingB"",
                  ""children"": []
                }
              ]
            },
            {
              ""id"": ""949133621472951133"",
              ""name"": ""Level04"",
              ""children"": [
                {
                  ""id"": ""949133621472951134"",
                  ""name"": ""BuildingA"",
                  ""children"": []
                },
                {
                  ""id"": ""949133621472951135"",
                  ""name"": ""BuildingB"",
                  ""children"": []
                }
              ]
            },
            {
              ""id"": ""949133621472951136"",
              ""name"": ""Roof"",
              ""children"": [
                {
                  ""id"": ""949133621472951137"",
                  ""name"": ""BuildingA"",
                  ""children"": []
                },
                {
                  ""id"": ""949133621472951138"",
                  ""name"": ""BuildingB"",
                  ""children"": []
                }
              ]
            }
          ]
        },
        {
          ""id"": ""949133621472951139"",
          ""name"": ""02Facade"",
          ""children"": [
            {
              ""id"": ""949133621473513959"",
              ""name"": ""BuildingA"",
              ""children"": [
                {
                  ""id"": ""949133621473513951"",
                  ""name"": ""EastElevation"",
                  ""children"": [
                    {
                      ""id"": ""949133621473513979"",
                      ""name"": ""GroundFloor"",
                      ""children"": []
                    },
                    {
                      ""id"": ""949133621473513980"",
                      ""name"": ""Level1(Podium)"",
                      ""children"": []
                    },
                    {
                      ""id"": ""949133621473513981"",
                      ""name"": ""Level2"",
                      ""children"": []
                    },
                    {
                      ""id"": ""949133621473513982"",
                      ""name"": ""Level3"",
                      ""children"": []
                    },
                    {
                      ""id"": ""949133621473513983"",
                      ""name"": ""Level4"",
                      ""children"": []
                    }
                  ]
                },
                {
                  ""id"": ""949133621473513973"",
                  ""name"": ""NorthElevation"",
                  ""children"": [
                    {
                      ""id"": ""949133621473513966"",
                      ""name"": ""GroundFloor"",
                      ""children"": []
                    },
                    {
                      ""id"": ""949133621473513967"",
                      ""name"": ""Level1(Podium)"",
                      ""children"": []
                    },
                    {
                      ""id"": ""949133621473513968"",
                      ""name"": ""Level2"",
                      ""children"": []
                    },
                    {
                      ""id"": ""949133621473513969"",
                      ""name"": ""Level3"",
                      ""children"": []
                    },
                    {
                      ""id"": ""949133621473513970"",
                      ""name"": ""Level4"",
                      ""children"": []
                    }
                  ]
                },
                {
                  ""id"": ""949133621473513974"",
                  ""name"": ""SouthElevation"",
                  ""children"": [
                    {
                      ""id"": ""949133621473513984"",
                      ""name"": ""GroundFloor"",
                      ""children"": []
                    },
                    {
                      ""id"": ""949133621473513985"",
                      ""name"": ""Level1(Podium)"",
                      ""children"": []
                    },
                    {
                      ""id"": ""949133621473513986"",
                      ""name"": ""Level2"",
                      ""children"": []
                    },
                    {
                      ""id"": ""949133621473513987"",
                      ""name"": ""Level3"",
                      ""children"": []
                    },
                    {
                      ""id"": ""949133621473513988"",
                      ""name"": ""Level4"",
                      ""children"": []
                    }
                  ]
                },
                {
                  ""id"": ""949133621473513972"",
                  ""name"": ""WestElevation"",
                  ""children"": [
                    {
                      ""id"": ""949133621473513961"",
                      ""name"": ""GroundFloor"",
                      ""children"": []
                    },
                    {
                      ""id"": ""949133621473513962"",
                      ""name"": ""Level1(Podium)"",
                      ""children"": []
                    },
                    {
                      ""id"": ""949133621473513963"",
                      ""name"": ""Level2"",
                      ""children"": []
                    },
                    {
                      ""id"": ""949133621473513964"",
                      ""name"": ""Level3"",
                      ""children"": []
                    },
                    {
                      ""id"": ""949133621473513965"",
                      ""name"": ""Level4"",
                      ""children"": []
                    }
                  ]
                }
              ]
            },
            {
              ""id"": ""949133621473513960"",
              ""name"": ""BuildingB"",
              ""children"": [
                {
                  ""id"": ""949133621473513975"",
                  ""name"": ""EastElevation"",
                  ""children"": [
                    {
                      ""id"": ""949133621473513971"",
                      ""name"": ""GroundFloor"",
                      ""children"": []
                    },
                    {
                      ""id"": ""949133621473513992"",
                      ""name"": ""Level1(Podium)"",
                      ""children"": []
                    },
                    {
                      ""id"": ""949133621473513993"",
                      ""name"": ""Level2"",
                      ""children"": []
                    },
                    {
                      ""id"": ""949133621473513994"",
                      ""name"": ""Level3"",
                      ""children"": []
                    },
                    {
                      ""id"": ""949133621473513995"",
                      ""name"": ""Level4"",
                      ""children"": []
                    }
                  ]
                },
                {
                  ""id"": ""949133621473513977"",
                  ""name"": ""NorthElevation"",
                  ""children"": [
                    {
                      ""id"": ""949133621473514001"",
                      ""name"": ""GroundFloor"",
                      ""children"": []
                    },
                    {
                      ""id"": ""949133621473514002"",
                      ""name"": ""Level1(Podium)"",
                      ""children"": []
                    },
                    {
                      ""id"": ""949133621473514003"",
                      ""name"": ""Level2"",
                      ""children"": []
                    },
                    {
                      ""id"": ""949133621473514004"",
                      ""name"": ""Level3"",
                      ""children"": []
                    },
                    {
                      ""id"": ""949133621473514005"",
                      ""name"": ""Level4"",
                      ""children"": []
                    }
                  ]
                },
                {
                  ""id"": ""949133621473513978"",
                  ""name"": ""SouthElevation"",
                  ""children"": [
                    {
                      ""id"": ""949133621473513989"",
                      ""name"": ""GroundFloor"",
                      ""children"": []
                    },
                    {
                      ""id"": ""949133621473513990"",
                      ""name"": ""Level1(Podium)"",
                      ""children"": []
                    },
                    {
                      ""id"": ""949133621473513991"",
                      ""name"": ""Level2"",
                      ""children"": []
                    },
                    {
                      ""id"": ""949133621473514012"",
                      ""name"": ""Level3"",
                      ""children"": []
                    },
                    {
                      ""id"": ""949133621473514013"",
                      ""name"": ""Level4"",
                      ""children"": []
                    }
                  ]
                },
                {
                  ""id"": ""949133621473513976"",
                  ""name"": ""WestElevation"",
                  ""children"": [
                    {
                      ""id"": ""949133621473513996"",
                      ""name"": ""GroundFloor"",
                      ""children"": []
                    },
                    {
                      ""id"": ""949133621473513997"",
                      ""name"": ""Level1(Podium)"",
                      ""children"": []
                    },
                    {
                      ""id"": ""949133621473513998"",
                      ""name"": ""Level2"",
                      ""children"": []
                    },
                    {
                      ""id"": ""949133621473513999"",
                      ""name"": ""Level3"",
                      ""children"": []
                    },
                    {
                      ""id"": ""949133621473514000"",
                      ""name"": ""Level4"",
                      ""children"": []
                    }
                  ]
                }
              ]
            }
          ]
        }
      ]
    }
  ]
}
    "),
    #"Converted to Table" = Record.ToTable(Source),
    #"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"),
    #"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"id", "name", "children"}, {"id", "name.1", "children"}),
    #"Expanded children" = Table.ExpandListColumn(#"Expanded Value1", "children"),
    #"Expanded children1" = Table.ExpandRecordColumn(#"Expanded children", "children", {"id", "name", "children"}, {"id.1", "name.2", "children.1"}),
    #"Expanded children.1" = Table.ExpandListColumn(#"Expanded children1", "children.1"),
    #"Expanded children.2" = Table.ExpandRecordColumn(#"Expanded children.1", "children.1", {"id", "name", "children"}, {"id.2", "name.3", "children"}),
    #"Expanded children2" = Table.ExpandListColumn(#"Expanded children.2", "children"),
    #"Expanded children3" = Table.ExpandRecordColumn(#"Expanded children2", "children", {"id", "name", "children"}, {"id.3", "name.4", "children.1"}),
    #"Expanded children.3" = Table.ExpandListColumn(#"Expanded children3", "children.1"),
    #"Expanded children.4" = Table.ExpandRecordColumn(#"Expanded children.3", "children.1", {"id", "name", "children"}, {"id.4", "name.5", "children"}),
    #"Expanded children4" = Table.ExpandListColumn(#"Expanded children.4", "children")
in
    #"Expanded children4"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.