Wednesday, January 24, 2024

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

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog