
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:
Be the first to comment