Flatten a parent-child hierarchy of unknown depth, with a separate sort/weight field

[aarongh]

Hi there,

I need to take a parent-child hierarchy of unknown/variable depth, and convert it into a structure that gives the depth level and the full path from the child to the parent.

I've found some very helpful articles on the topic, especially this one: https://pivotalbi.com/dynamically-flatten-a-parent-child-hierarchy-using-power-query-m/

However, I have an addition requirement, in that the structure should be in a specifc order. For example:

Parent	Child	Weight
	CompanyName	1
CompanyName	Department A	3
Department A	Unit X	9
Department A	Unit Y	7
Department A	Unit Z	3
Company Name	Department B	1
Department B	Unit P	2
Department B	Unit S	5

So, the final structure needs to be built dynamically, but sortable using the weights above, to give something like this:

Parent	Sort	Depth	Level 1	Level 2	IsLeaf
CompanyName	1	1			False
CompanyName	1.2	2	Department B		False
CompanyName	1.2.2	3	Department B	Unit P	True
CompanyName	1.2.5	3	Department B	Unit S	True
CompanyName	1.3	2	Department A		False
CompanyName	1.3.3	3	Department A	Unit Z	True
CompanyName	1.3.7	3	Department A	Unit Y	True
CompanyName	1.3.9	3	Department A	Unit X	True

Unfortunately, the PowerQuery M code in the link above is way above my head, and I can't figure out how to modify it to use the Weight as a sorting mechanism.

Any help or pointers would be really appreciated!  

[smpa01]

@aarongh  you data probably corresponds to the json below. Where do you expect the `unknown depth` in this dataset to occur and how does the resulting dataset needs to look like with that change in depth occuring?

const data = [{
    "levelOneAttr": {
        "companyName": 1,
        "weight": 1,
        "levelTwoAttr": [{
                "deptName": "A",
                "weight": 3,
                "children": [
                    { "deptName": "X", "weight": 9 },
                    { "deptName": "Y", "weight": 7 },
                    { "deptName": "Z", "weight": 3 }
                ]
            },
            {
                "deptName": "B",
                "weight": 1,
                "children": [
                    { "deptName": "P", "weight": 2 },
                    { "deptName": "S", "weight": 5 }

                ]
            }

        ]
    }
}];

.

[aarongh]

Sorry, I just saw a mistake in the results table!

The 'Sort' string is derived from the 'Weight' field. As the 'Weight' value for Department B is '1', then then 'Sort' values for Department B should start with 1.1, not 1.2.