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

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

Hi Anonymous ,

You could refer to the following codes( Group and Merge ).

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRMlSK1YGxjcBsE1MzINsYiW2CxDZVio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1"}, {{"Count", each List.Sum([Column2]), type number}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Column1"},#"Grouped Rows", {"Column1"},  "Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Count"}, {"Grouped Rows.Count"})
in
    #"Expanded Grouped Rows"

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:

Hello guys, can you suggest a method to make rolling sum in PowerQuery : lets suppose I have Clients and Area and Months - and I want to make sumifs for each Client and Area where @Month>=Month

Add new empty query and paste this into the advanced editor:

let
  Source = #table({"Col1", "Col2", "Col3"}, {{1,"Some", 1}, {1, "other", 2}, {1, "other", 3}, {2, "irrelevant", 4}, {2, "data", 5}}),
    #"Sub total" = Table.AddColumn(Source, "Col4", each List.Sum(Table.Column(Table.SelectRows(Source, (recordFilter) => recordFilter[Col1]=[Col1]), "Col3"))),
    #"Grand total" = Table.AddColumn(#"Sub total", "Col5", each List.Sum(#"Sub total"[Col3]))

in
    #"Grand total"

Then take a look at the "Sub total" to see how it works.

It's specifically this part of the code that does the sub total aka sumif

List.Sum(Table.Column(Table.SelectRows(Source, (recordFilter) => recordFilter[Col1]=[Col1]), "Col3"))

Sub total is calculated over Col1 with Col3 as input.

In sumifs terms (see this link for sum if syntax):

Col3 = sum_range

recordFilter[Col1]=range

[Col1]=criteria

If you need to divide the sub total over several columns, here's an example using col1 and col2: 

List.Sum(Table.Column(Table.SelectRows(Source, (recordFilter) => recordFilter[Col1]=[Col1] and recordFilter[Col2]=[Col2]), "Col3"))

Using List.Sum will take forever to run

expecially with a complex mereged from different tables.

I do have the best practice for this.