Forum Discussion

MagikJukas's avatar
MagikJukas
Resolver III
2 years ago
Solved

Merge table in loop

Hello,

assuming I have a table like this:

FatherSon
AB
BC
CD
DE
EF
FG
GH

 

 

I would like to merge this table to obtain:

FatherSon
AB
AC
AD
AE
AF
AG
AH
BC
BD
BE
BF
BG
BH

...

 

 

how can it be achieved?

 

thanks

Need Help
  • AlienSx's avatar
    AlienSx
    2 years ago
    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

6 Replies

  • AlienSx's avatar
    AlienSx
    Super User
    2 years ago
    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
  • PwerQueryKees's avatar
    PwerQueryKees
    Super User
    2 years ago

    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's avatar
      MagikJukas
      Resolver III
      2 years ago

      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 

  • ZhangKun's avatar
    ZhangKun
    Super User
    2 years ago
    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's avatar
      ZhangKun
      Super User
      2 years ago

      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's avatar
    PwerQueryKees
    Super User
    2 years ago

    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"