How to create Parent and Child Hierarchy using Power Query in Power BI

How to create Root Node and Leaf Node Hierarchy using M-Query in Power BI

Scenario:
Suppose we have a sample data with ParentNode and ChildNode columns as follows.

Lets say:

A1 is the Parent Node for the Child Node B1. This Child Node can be RootNode or a Parent Node for other Child Nodes as per below.

B1 is the Parent Node for {C1, D1, E1,F1}.

C1 is the Parent Node for {G1, H1}.

Please note that, the A1 and J1 are the Root Nodes and they do not have the Parent Node assigned.
Based on this sample, we have to create a [NodeHierarchy] column with values as per below:
The Node Hierarchy for A1 = A1

The Node Hierarchy for F1 = A1|B1|F1

The Node Hierarchy for G1 = A1|B1|C1|G1
The Node Hierarchy for H1 = A1|B1|C1|H1

We can achieve this Scenario using the below Power Query (M-Query):

let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Nc07DoNADIThu0w9BQssScoACYg8pKRdcf9rgPR7JRf+RvK4FCVZ59yTdhe1rNaIO1Zrwn31jHP1Aw/VT3zh1FrwtXrFN75vKDU0W68Iotp6R9DSbX0i6Ci3vhH0tFu/CDIPrP8Z7Ac=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [NodeID = _t, ParentNode = _t, ChildNode = _t]),

    ChangeType = Table.TransformColumnTypes(Source,{{"NodeID", Int64.Type}, {"ParentNode", type text}, {"ChildNode", type text}}),

cc_NodeHierarchy = 

Table.AddColumn(ChangeType, "NodeHierarchy", 

each let

            p=[ParentNode],c = [ChildNode], myTable = ChangeType, pc ="ParentNode", cc = "ChildNode" 

        in 

            let 

                myList = {c} & List.Generate(()=> [x=0,y=p,w=1], 

                    each [w]>0, 

                    each [z=[y], x=Table.Column(Table.SelectRows(myTable, 

                    each Record.Field(_,cc)=z),pc), y=x{0}, w=List.Count(x)],

                    each [y])

            in  Text.Combine(List.Reverse(List.RemoveItems(

                List.Transform(myList, each Text.From(_)),{null,""})),"|")),

    cc_ChildNodeLevel = Table.AddColumn(cc_NodeHierarchy, "ChildNodeLevel", 

        each if List.Count(Text.Split([NodeHierarchy],"|"))=1 then "RootNode" 

        else "Node Level " & Number.ToText(List.Count(Text.Split([NodeHierarchy],"|"))))

in

    cc_ChildNodeLevel

Result:

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------