Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Consolidate multiple tables with comment text columns

Hi, could anyone please help with this tables consolidation. I have multiple tables that nested in a table, let's say 2 tables, table 1 & 2, the number of tables is dynamic. The columns inside those ...
  • AlienSx's avatar
    2 years ago
    let
        nested = Table.FromRecords(
            {[Code = 6100,
            Custom = Table.FromRecords(
                {[Desc = "Item 1", Prior = 8, Current = 9, Var = 1, Comment = "reason a"],
                [Desc = "Item 2", Prior = 5, Current = 4, Var = -1, Comment = "reason b"]}
            )],
            [Code = 6200,
            Custom = Table.FromRecords(
                {[Desc = "Item 1", Prior = 6, Current = 11, Var = 5, Comment = "reason c"],
                [Desc = "Item 2", Prior = 4, Current = 7, Var = 3, Comment = "reason d"],
                [Desc = "Item 3", Prior = 1, Current = 3, Var = 2, Comment = "reason e"]}
            )]
            }
        ),
        idx = Table.AddIndexColumn(nested, "idx", 1, 1),
        idc = Table.TransformColumns(idx, {"idx", (x) => "Comment table " & Text.From(x)}),
        comm_columns = List.Transform({1..Table.RowCount(nested)}, (x) => "Comment table " & Text.From(x)),
        xp_tbl = Table.ExpandTableColumn(idc, "Custom", {"Desc", "Prior", "Current", "Var", "Comment"}),
        group = Table.Group(
            xp_tbl,
            "Desc",
            List.Transform(
                {"Prior", "Current", "Var"},
                (x) => {x, each List.Sum(Table.Column(_, x))}
            ) & 
            {{"c", (x) => Record.FromList(x[Comment], x[idx])}}
        ),
        xp_rec = Table.ExpandRecordColumn(group, "c", comm_columns)
    in
        xp_rec