Consolidate multiple tables with comment text columns

[don]

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 tables are the same, e.g. Desc, Prior, Current... How do I consolidate those multiple tables into table 3 that the text comments are now displayed in columns? Thank you!

 

[AlienSx · Accepted solution]

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

[don]

This is great, it works perfectly, thank you so much AlienSx 🙂

[dufoq3]

Hi @don,

Result

let
    Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WyixJzVUwVNJRsgBiSyAGsYtSE4vz8xQSlWJ1oCqMgKKmQGwCxLpISpKUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Desc = _t, Prior = _t, Current = _t, Var = _t, Comment = _t]),
    Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WyixJzVUwVNJRMgNiQxDDFIiLUhOL8/MUkpVidaBKjICiJkBsDsTGCBUpCBUgUUOorBFERQpQRapSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Desc = _t, Prior = _t, Current = _t, Var = _t, Comment = _t]),
    MergedQueries = Table.NestedJoin(Table1, {"Desc"}, Table2, {"Desc"}, "Table2", JoinKind.FullOuter),
    RenamedColumns = Table.RenameColumns(MergedQueries,{{"Comment", "Comment table1"}}),
    Ad_FinalTable = Table.AddColumn(RenamedColumns, "Final Table", each 
        [ a = Table.AddColumn(Table.FromRecords({_}), "Comment table2", (x)=> [Table2]{0}[Comment]),
          b = Table.TransformColumns(a, List.Transform({"Desc", "Prior", "Current", "Var"}, (colName)=> {colName, (x)=>
                if colName = "Desc" and x = null then Record.Field([Table2]{0}, colName) else
                if colName = "Desc" then x
                else (Number.From(x)??0) + (Number.From(Record.Field([Table2]{0}, colName))??0), type number})),
          c = Table.RemoveColumns(b, {"Table2"})
        ][c], type table),
    CombinedFinalTable = Table.Combine(Ad_FinalTable[Final Table])
in
    CombinedFinalTable