Merge table in loop

[MagikJukas]

Hello,

assuming I have a table like this:

Father	Son
A	B
B	C
C	D
D	E
E	F
F	G
G	H

I would like to merge this table to obtain:

Father	Son
A	B
A	C
A	D
A	E
A	F
A	G
A	H
B	C
B	D
B	E
B	F
B	G
B	H
...

how can it be achieved?

thanks

[PwerQueryKees]

I adapted a function I already had. Not as slick as @AlienSx ...

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    getParents = (SourceTable as table, CurrentRecord as record, KeyFieldName as text, ParentFieldName as text) => 
        let 
            Parents = Table.SelectRows(SourceTable, each Record.Field(_, KeyFieldName) = Record.Field(CurrentRecord, ParentFieldName))
        in
            if Table.RowCount(Parents) = 1 then // multiple parents not allowed!
                let
                    parent = Parents{0}
                in
                    Table.Combine({@getParents(SourceTable, parent, KeyFieldName, ParentFieldName),Parents}) 
            else 
                Table.FromRecords({})
        ,
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Father", type text}, {"Son", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Fathers", each List.Combine({{[Father]},try getParents(#"Changed Type", _, "Son", "Father")[Father] otherwise {}})),
    #"Expanded Fathers1" = Table.ExpandListColumn(#"Added Custom", "Fathers")
in
    #"Expanded Fathers1"

[AlienSx · Accepted solution]

let
    Source = your_table,
    rec = Record.FromList(Source[Son], Source[Father]), 
    back_to_back = (s, name) => 
        [next_name = Record.FieldOrDefault(rec, name),
        next = if next_name is null then s else @back_to_back(s & {next_name}, next_name)][next],
    add_col = Table.AddColumn(Table.RemoveColumns(Source, {"Son"}), "Son", (x) => back_to_back({}, x[Father])),
    expand = Table.ExpandListColumn(add_col, "Son")
in
    expand

[ZhangKun]

let
    data = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HcZJDQAwDAMwLnmXxNZrHKryp7EoH8szODBcrA01uOZcaMGlllxpxbXW3MPuBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Father = _t, Son = _t]), 
    recData = Record.FromList(data[Son], data[Father]), 
    result = 
        Table.FromRows(
            List.Combine(
                List.Transform(
                    data[Father], 
                    (f) => 
                    let 
                        a = 
                            List.Generate(
                                () => f, 
                                each not(Record.FieldOrDefault(recData, _, null) is null), 
                                each Record.FieldOrDefault(recData, _), 
                                each Record.FieldOrDefault(recData, _)
                            ), 
                        b = List.Zip({List.Repeat({f}, List.Count(a)), a})
                    in 
                        b
                )
            ), 
            Table.ColumnNames(data)
        )
in
    result

[ZhangKun]

AlienSx's answer was so concise and brilliant, it gave me some inspiration to make the code less complicated

let
    data = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HcZJDQAwDAMwLnmXxNZrHKryp7EoH8szODBcrA01uOZcaMGlllxpxbXW3MPuBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Father = _t, Son = _t]), 
    recData = Record.FromList(data[Son], data[Father]), 
    fx = (v) =>
        List.Generate(
            () => v, 
            each not(Record.FieldOrDefault(recData, _, null) is null), 
            each Record.FieldOrDefault(recData, _), 
            each Record.FieldOrDefault(recData, _)
        ), 
    result = 
        Table.ExpandListColumn(
            Table.AddColumn(
                data[[Father]], 
                "Son", 
                each fx([Father])
            ), 
            "Son"
        )
in
    result

[PwerQueryKees]

Am I interpreting this correctly: A row in the result table means: The father is the father, grand father or grand grand father etc. of the son? Otherwise it would just be a carthesian product of the table with itself...

I'll have a look if I can solve this...

[MagikJukas]

You can solve it manually, by keeping on merging Father with Son, Son1,Son 2,... until you get null results.

then you can pivot the columns and you have it.

the problem is that it is not dynamic. if there number of sons changes, you will have to update the query accordingly.

thanks for your engagement @PwerQueryKees