Forum Discussion

SaaM's avatar
SaaM
Helper II
5 years ago
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 ... ...
Need Help
  • Jimmy801's avatar
    Jimmy801
    5 years ago

    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

     

edhans's avatar
edhans
Community Champion
5 years ago

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

SaaM's avatar
SaaM
Helper II
5 years ago

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