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

[Anonymous]

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 column B based on a match to Field 1. I realize that I can group by Field 1, but I need to maintain all of the rows.

How can I replicate sumif like Excel in Power Query (not DAX)?

 

[Donle]

Using List.Sum will take forever to run

expecially with a complex mereged from different tables.

I do have the best practice for this.

[MAFRE]

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"))

[Anonymous]

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

[Anonymous]

@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:

[Anonymous]

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

[v-eachen-msft]

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"

[camargos88 · Accepted solution]

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.

[SandorT]

It solves the problem, but please note this is a calculation heavy process. In case of a bigger database its not really effective.

[Rygaard]

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]

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.

[Anonymous]

Hello, I tried your query but it shows the following error: "Expression.Error: A cyclic reference was encountered during evaluation."

This is my query:

= let
_Item = [#"ID"]
in
List.Sum(
Table.SelectRows(#"DATA",each
[#ID"] = _Item)[#"COLLECTIONS"])

Table Name = Data

Column with ID number = ID (I have different collections for the same ID (differente rows) that I want to summarize)

Column with collection numbers = COLLECTIONS

Basically I want to add all the collections for every single ID, and show the total in every row (according to that ID).

Any thoughts on how to fix it?

Thank you.

[Harrisonbeck]

I have the same error. Did you ever get this fixed? 

[Dave_Clark]

Your example worked well, even with a self-reference for the criterion. How would you do this for two or more criteria?

[camargos88]

Hi @Dave_Clark ,

You can use more conditions with variables, like:

let

_item = [Column1],

_item2 = [ColumnXXX]

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

[MAFRE]

It works great, any chance you could explain how this works or any reference to a post explaining it? I understand what happens but not how you got M to do this, first time I've seen the use of a second LET to filter/sum

Edit: Found this which explains it and much more:
https://bengribaudo.com/blog/2017/11/17/4107/power-query-m-primer-part1-introduction-simple-expressi...