Forum Discussion
Create sumif in PowerQuery (not DAX, and not use group by feature)
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.
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.
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-expressions-let
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?
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.
It solves the problem, but please note this is a calculation heavy process. In case of a bigger database its not really effective.
