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
ZhangKun's avatar
ZhangKun
Super User
1 year 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
    1 year 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