Flatten Parent-Child Hierarchy table with jumping levels, without M and not DAX

[juncr]

Hi Power BI Community,

I am trying to flatten a Parent-Child Hiearchy table (with jumping levels) in Power Query and I would appreciate any tips.

The context: the parent-child hierarchy table is coming from a computerized maintenance management system. 

The tricky part for me: jumping hierarchy levels. And tranforming the table in M instead of DAX.

This is a simplified version of the hierarchy with jumping levels. 

What I want to achieve is:

AS_IS: Parent-Child Hiearchy table (The original table has ~ 300 K rows.)

Child key	Parent key	Hierarchy level
Comp_A	Subunit_A	6-COMPONENT
Subunit_A	Unit_A	5-SUBUNIT
Unit_A	Equipment_A	4-UNIT
Equipment_A	ProdArea_A	3-EQUIPMENT
ProdArea_A	Plant_A	2-PRODAREA
Plant_A		1-PLANT
Comp_B	Unit_B	6-COMPONENT
Unit_B	Equipment_B	4-UNIT
Equipment_B	ProdArea_B	3-EQUIPMENT
ProdArea_B	Plant_A	2-PRODAREA

TO-BE: Flattned Hierarchy table with conditionals

Child key	1-PLANT	2-PRODAREA	3-EQUIPMENT	4-UNIT	5-SUBUNIT	6-COMPONENT	Conditional explanation
Comp_A	Plant_A	ProdArea_A	Equipment_A	Unit_A	Subunit_A	Comp_A
Subunit_A	Plant_A	ProdArea_A	Equipment_A	Unit_A	Subunit_A
Unit_A	Plant_A	ProdArea_A	Equipment_A	Unit_A
Equipment_A	Plant_A	ProdArea_A	Equipment_A
ProdArea_A	Plant_A	ProdArea_A
Plant_A	Plant_A
Comp_B	Plant_A	ProdArea_B	Equipment_B	Unit_B	Unit_B	Comp_B	If there is a "jump" in levels, return the next parent key
Unit_B	Plant_A	ProdArea_B	Equipment_B	Unit_B
Equipment_B	Plant_A	ProdArea_B	Equipment_B
ProdArea_B	Plant_A	ProdArea_B

I was able to flatten this table with DAX, using PATH, PAHTITEM, LOOKUPVALUE and SWITCH for each hiearchy level column. However, the target is to create the flattned table in dataflows and use it in different reports in Power BI. Therefore I need to achieve the same result with M syntax in Power Query.

There are no cases of multiple parents.

Since the case is fairly complicated, I would appreciate tips to reproduce the functions in M for PATH and PATHITEM. From there I can try to parse by a PATH column by delimiter and add the conditionals by column, I believe.

[v-yalanwu-msft · Accepted solution]

Hi, @juncr ;

You could try it.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PLYh3VNJRCi5NKs3LLAGzzXSd/X0D/P1c/UKUYnWiUeRCYQxT3eBQp1A/T4gSuLBrYWlmQW5qHoRnogtXgSoRUJSf4liUmgjmGOu6BoZ6BvjC7EORDMhJhOox0g0I8ndxDHJ1hKiCSwCRoW6AjyNUO9hPTjC3OmHxEFwC4SgnXK51QnatEz7XOuF0bSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Child key" = _t, #"Parent key" = _t, #"Hierarchy level" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Child key", type text}, {"Parent key", type text}, {"Hierarchy level", type text}}),
    myfunction = (ChildCol,ParentCol,CurrentParent)=>
                    let
                    mylist=Table.Column(Table.SelectRows(#"Changed Type",each Record.Field(_,ChildCol)=CurrentParent),ParentCol),
                    result=Text.Combine(mylist)
                    in
                   
                    Text.TrimEnd(
                    if result ="" then ""  else @ result & "|" & @ myfunction(ChildCol,ParentCol,result),
                   "|"),
    Path = Table.AddColumn(#"Changed Type", 
                            "Path", each 
                                    Text.Trim(
                                        Text.Combine( List.Distinct(
                                                        List.Reverse(
                                                            List.RemoveItems(
                                                                Text.Split(myfunction("Child key","Parent key",[Child key]),"|"),{""}
                                                            )
                                                        )
                                                        &{[Parent key],[Child key]})
                                            ,
                                            "|"
                                        )
                                             ,"|"
                                    )
            )
in
    Path

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.