Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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"
Try adding Table.Buffer() around the Table.Group() step. That sometimes helps to speed up this kind of operation.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingI 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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.