Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

 

sumif.png 

1 ACCEPTED SOLUTION
camargos88
Community Champion
Community Champion

Hi @Anonymous ,

 

Try this code for a custom column:

 

 

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

 

 

Capture.PNG

 

Change the bold part for the last step name.



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

14 REPLIES 14
Donle
New Member

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
Frequent Visitor

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
Not applicable

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

PIG_BENlS_0-1664199853743.png

 

Anonymous
Not applicable

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

 

image.png

 

 

which results in:

 

image.png

 

 

Anonymous
Not applicable

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
Community Support
Community Support

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"

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
camargos88
Community Champion
Community Champion

Hi @Anonymous ,

 

Try this code for a custom column:

 

 

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

 

 

Capture.PNG

 

Change the bold part for the last step name.



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



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

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
Not applicable

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.

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

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

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



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



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...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors