Forum Discussion

MagikJukas's avatar
MagikJukas
Icon for Resolver III rankResolver III
2 years ago
Solved

Merge table in loop

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 ...
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
PwerQueryKees's avatar
PwerQueryKees
Icon for Super User rankSuper 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"