March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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)?
Solved! Go to 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.
Using List.Sum will take forever to run
expecially with a complex mereged from different tables.
I do have the best practice for this.
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"))
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
"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:
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"
It solves the problem, but please note this is a calculation heavy process. In case of a bigger database its not really effective.
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.
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])
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...
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
61 | |
55 | |
27 | |
17 | |
12 |