Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Create sumif in PowerQuery (not DAX, and not use group by feature)

I have a step in PowerQuery where I want to do a sumif (like in Excel). See the below picture.   In Power Query I have Field 1 and Field 2 and I want to add what is in column C.  Column C sums colu...
Need Help
  • camargos88's avatar
    camargos88
    6 years ago

    Hi Anonymous ,

     

    Try this code for a custom column:

     

     

    let _item = [Column1] in
    List.Sum(
    Table.SelectRows(#"Changed Type", each [Column1] = _item)[Column2])

     

     

     

    Change the bold part for the last step name.

camargos88's avatar
camargos88
Community Champion
6 years ago

Hi Anonymous ,

 

Try this code for a custom column:

 

 

let _item = [Column1] in
List.Sum(
Table.SelectRows(#"Changed Type", each [Column1] = _item)[Column2])

 

 

 

Change the bold part for the last step name.

Rygaard's avatar
Rygaard
Resolver I
4 years ago

the "LET-....." is working, and fast inside Power Query but it took like 10 min to load into excel

so opted to split the query in 2, one where i aggrigate, and then in the other i do a merge.  dont know why but for it it was like a factor 1000 faster for the enduser

  • kswaffield's avatar
    kswaffield
    New Member
    4 years ago

    Hi,

     

    The "LET-..." solution worked for me too, but lik you it is super slow to load into Excel.

     

    Can you explain a bit more what you did to get around this and how you split the query in 2.  I'm quite new to Power Query so it may be obvious but I don't really even know where to start.