Scenario:
Suppose we have a sample data with ParentNode and ChildNode columns as follows.
Suppose we have a sample data with ParentNode and ChildNode columns as follows.
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):
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:
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.