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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

the natural way to do that would be via group by, but just to partecipate at yours nice exercise, I propose this:

 

 

 

 

  Table.AddColumn(tab, "SumByGrp", each  List.Accumulate(List.PositionOf(tab[col1],[col1], Occurrence.All),0,(s,c)=>s+tab[col2]{c}) )

 

 

 

the main difficulty was finding a way to slice the list, with the list of indexes found, since there is no function in the library that does this. But the List.Accumulate can also adapt to fulfill this task