Forum Discussion
Performance issue - expanding column with rowcount
Hi all,
I am facing a performance issues while trying to do a group by and row count and expanding table.
I have tried many options but the result is always the same, very slow performance ...
I Group by my table 1 then join with table 2 then develop my new column.
Option 1:
#"myNewCol" = let
table1Filtred = Table.SelectRows(table1, (sel)=> sel[#"item"] <> null),
table1Filtred_tmp = Table.Group(table1Filtred, {"id"}, {{"DeveloppedNewCol", each Table.RowCount(_), Int64.Type}}),
#"MergedQueries" = Table.NestedJoin(table2, {"id"}, table1Filtred_tmp , {"id"}, "NewCol", JoinKind.LeftOuter),
#"ExpandedTable" = Table.ExpandTableColumn(#"MergedQueries", "NewCol", {"DeveloppedNewCol"}, {"DeveloppedNewCol"})
in
#"ExpandedTable"
Option 2:
#"myNewCol" = let
table1Filtred = Table.SelectRows(table1, (sel)=> sel[#"item"] <> null),
table1Filtred_tmp = Table.Group(table1Filtred, {"id"}, {{"DeveloppedNewCol", each Table.RowCount(_), Int64.Type}}),
#"MergedQueries" = Table.NestedJoin(table2, {"id"}, table1Filtred_tmp , {"id"}, "NewCol", JoinKind.LeftOuter),
#"ExpandedTable" = Table.ExpandTableColumn(#"MergedQueries", "NewCol", List.RemoveItems(Table.ColumnNames(#"MergedQueries"[#"DeveloppedNewCol"]{0}), {"id"}))
in
#"ExpandedTable"
The step that takes a lot is while expanding the column as I think it recalculates the field (rowcount() from table 1) for every id.
A snapshot of my table 2 after joining with table 1 and before expandingcolumn.
Thanks in advance
kind regards
Saam
Hello SaaM
try this approach and let us know
let table1Filtred = Table.SelectRows(table1, (sel)=> sel[#"item"] <> null), table1Filtred_tmp = Table.Buffer(Table.Group(table1Filtred, {"id"}, {{"DeveloppedNewCol", each Table.RowCount(_), Int64.Type}})), #"MergedQueries" = Table.NestedJoin(Table.Buffer(table2), {"id"}, table1Filtred_tmp , {"id"}, "NewCol", JoinKind.LeftOuter), #"ExpandedTable" = Table.AggregateTableColumn(#"MergedQueries", "NewCol", {{"DeveloppedNewCol", List.Sum, "DeveloppedNewCol"}}) in #"ExpandedTable"If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
5 Replies
Hello SaaM
try this approach and let us know
let table1Filtred = Table.SelectRows(table1, (sel)=> sel[#"item"] <> null), table1Filtred_tmp = Table.Buffer(Table.Group(table1Filtred, {"id"}, {{"DeveloppedNewCol", each Table.RowCount(_), Int64.Type}})), #"MergedQueries" = Table.NestedJoin(Table.Buffer(table2), {"id"}, table1Filtred_tmp , {"id"}, "NewCol", JoinKind.LeftOuter), #"ExpandedTable" = Table.AggregateTableColumn(#"MergedQueries", "NewCol", {{"DeveloppedNewCol", List.Sum, "DeveloppedNewCol"}}) in #"ExpandedTable"If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
JimmyIt worked ! The time execution was divided by 5
Thanks
Could you try this variation and letus kwnow?
let table1Filtred = Table.Buffer(Table.SelectRows(table1, (sel)=> sel[#"item"] <> null)), table1Filtred_tmp = Table.Group(table1Filtred, {"id"}, {{"DeveloppedNewCol", each Table.RowCount(_), Int64.Type}}), #"MergedQueries" = Table.NestedJoin(Table.Buffer(table2), {"id"}, table1Filtred_tmp , {"id"}, "NewCol", JoinKind.LeftOuter), #"ExpandedTable" = Table.AggregateTableColumn(#"MergedQueries", "NewCol", {{"DeveloppedNewCol", List.Sum, "DeveloppedNewCol"}}) in #"ExpandedTable"
Try adding Table.Buffer() around the Table.Group() step. That sometimes helps to speed up this kind of operation.
I tried to add as suggested the Table Buffer:
- before
- after
- before and after
the Table.group but the performance remains roughly the same.
I was wondering if instead of using the Table.Rowcount(_) I can use something else that gives me directly the value without expand the column.
Inside every Table of my non Expandedcolumn I have the id and the row count and when i expand the column I select only the rowCount field :
I do not know if i can process differently, maybe in DAX ?
Thanks
Kind regards
Saam
