cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SaaM
Helper II
Helper II

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.

SaaM_0-1616688074215.png

 

Thanks in advance

 

kind regards

Saam

1 ACCEPTED SOLUTION
Jimmy801
Community Champion
Community Champion

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

 

View solution in original post

5 REPLIES 5
Jimmy801
Community Champion
Community Champion

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

 

View solution in original post

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"
edhans
Super User
Super User

Try adding Table.Buffer() around the Table.Group() step. That sometimes helps to speed up this kind of operation.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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 :

SaaM_0-1616752757306.png

 

I do not know if i can process differently, maybe in DAX ?

 

Thanks

Kind regards

Saam

 

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors