Forum Discussion

MagikJukas's avatar
MagikJukas
Resolver III
1 year 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
    1 year 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
1 year 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
1 year 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