Forum Discussion
Solved
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 ... ...
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
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
It 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"
