Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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!
Solved! Go to 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
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
This is great, it works perfectly, thank you so much AlienSx 🙂
Hi @Anonymous,
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