Forum Discussion

IlseV's avatar
IlseV
Frequent Visitor
3 years ago

parent child extension

Hi everyone,  I hope you can help me.  I have a table with 2 columns. 1 is the parent, 2 is the child But some childs can be parents themselves and again and again, so multiple levels. I would li...
Need Help
CNENFRNL's avatar
CNENFRNL
Community Champion
3 years ago

 

let
    fx_Recur = (x as text, l as list) =>
        let lst = {Text.Upper(x)} & l in try Fx_Recur(Text.Upper(Prt{List.PositionOf(Chd, x, Occurrence.First, Comparer.OrdinalIgnoreCase)}), lst) otherwise lst,

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk4tUXBU0lEqLk1KLC6uNFSK1YlWKoYKpmXmlKQWoQgl5VdA+DD1OkrBEKYRunhOYlJqDrKgEcie5KLUcnTBpPycEqXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Parent = _t, Child = _t]),
    Prt = Source[Parent],
    Chd = Source[Child],
    Hierarchy = Table.AddColumn(Source, "h", each fx_Recur([Parent], {Text.Upper([Child])})),
    #"Selected Rows" = Table.SelectRows(Hierarchy, each not List.Contains(Prt, [Child], Comparer.OrdinalIgnoreCase))[h],
    Expanded = Table.Combine(List.Transform(#"Selected Rows", each Table.FromRows({_})))
in
    Expanded