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

Anonymous 

 "I realize that I can group by Field 1, but I need to maintain all of the rows."

 

if you have no other restrictions on the use of the groupBy function besides having all the original rows of the table, you can continue to use groupby with the following scheme. In which I deal with the case of the double condition.:

 

 

let 

    grp = Table.Group(tabC1C2C3, {"col1", "col3"}, {{"sum", each List.Sum([col2]), type number}, {"col2", each _[col2]}}),
    te = Table.ExpandListColumn(grp, "col2")
in
    te

 

tab source:

 

 

 

which results in: