Forum Discussion
Anonymous
2 years agoNot applicable
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 ...
- 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
AlienSx
2 years agoSuper User
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_recAnonymous
2 years agoNot applicable
This is great, it works perfectly, thank you so much AlienSx 🙂