parent child extension

[IlseV]

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 like to have this all in 1 view. So a flat BOM with all details. 

Is this possible and how to do so?

This is a small example:

SO I have this list with only 2 columns: Parent and Child

Parent	Child
set A	subassy1
set A	filter
set A	box
subassy1	subassy2
subassy1	label
subassy2	screw
subassy2	bolt

And I want this view

Parent	Child level 1	Child level 2	Child level 3	Child level 4
set A	subassy1	subassy2	screw	-
set A	subassy1	subassy2	bolt	-
set A	subassy1	label	-	-
set A	filter	-	-	-
set A	box	-	-	-

[jgordon11]

This might take 2 to 6 hours on 120000 rows.  Start it before leaving work for the day and it should be done when you return.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tbl = Table.Buffer(Table.RenameColumns(Source,{{"Child", "Child level 1"}})),
    chld = List.Buffer(tbl[Child level 1]),
    nokids = Table.SelectRows(tbl, each not List.Contains(chld, [Parent] )),
    haskids = Table.Buffer(Table.SelectRows(tbl, each List.Contains(chld, [Parent] ))),

    lsta = List.Accumulate({1..12}, {nokids, #table({"Parent","Child level 1"}, {})}, (s,c)=>
            let 
                lastcol = List.Last(Table.ColumnNames(s{0})),
                ct = List.Count(List.RemoveNulls(Table.Column(s{0},lastcol)))
            in   
                if ct>0 then  
                    let  
                        tbl1 =Table.NestedJoin(s{0}, {"Child level " & Text.From(c)}, haskids, {"Parent"}, "Child level " & Text.From(c+1)),
                        tbl1e = Table.ExpandTableColumn(tbl1, "Child level " & Text.From(c+1), {"Child level 1"}, {"Child level " & Text.From(c+1)}),
                        tbl1k = Table.SelectRows(tbl1e, each Record.Field(_, "Child level " & Text.From(c+1)) <> null)
                    in   
                        {tbl1k, s{1} & Table.SelectRows(tbl1e, each Record.Field(_, "Child level " & Text.From(c+1)) = null)}
                else s),
    tbla = lsta{0} & lsta{1}
in
    tbla

[CNENFRNL]

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

[jgordon11]

How many rows are in your starting table?  I have a solution but it gets very slow if the number of rows is large.  For 2000 rows (and up to ten deep Child levels) it takes about 45 seconds to run.  If your starting table is a lot larger it is probably too slow.

[IlseV]

Hi, the starting table has about 120.000 rows 😞