Performance issue - expanding column with rowcount

[SaaM]

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

[Jimmy801 · Accepted 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

[SaaM]

It worked ! The time execution was divided by 5

Thanks

[Anonymous]

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]

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

[SaaM]

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