Forum Discussion
SUMIFS functionality using Power Query M advanced editor
Hello all, in need of some help to recreate the functionality of this SUMIFS formula. I'm still new on M Code and I'm really struggling with this one. I'm using this SUMIFS to compute a running total based on each days transactions.
This is the SUMIFS in question:
"=SUMIFS([QTY],[PART_ID],[@[PART_ID]],[COUNT_REL_OPERATION],[@[COUNT_REL_OPERATION]],[COMPLETED_QTY],">"&[@[COMPLETED_QTY]]) "
We could simplify but just saying:
"=SUMIFS(SUM RANGE, CRITERIA RANGE 1, CRITERIA 1, CRITERIA RANGE 2, CRITERIA 2, CRITERIA RANGE 3,">"&CRITERIA 3) "
I found stuff online using the Let function but I'm not able to figure out how to include multiple ranges and multiple criterias, especially when one of the critarias has to be > (more than) instead of equal.
Here is where I got so far:
let
Range1 = ([PART_ID])
in List.Sum(
Table.SelectRows(#"Changed Type", each [PART_ID] = Range1)
[QTY])
Hi Villeminl
My guess is your column [LAST TOUCHED] is added after step #"Changed Type", you are now calling it in this table which the column has not yet existed...so let's call the step to add [LAST TOUCHED] as "step1" for now, then it should be like this
= [CurID = [PART_ID], CurOp=[COUNT_REL_OPERATION], CurQty=[COMPLETED_QTY], CurDate=[DUE DATE], CurTouch=[LAST TOUCHED], res=List.Sum( Table.SelectRows(step1, each [PART_ID]=CurID and [COUNT_REL_OPERATION] =CurOp and [COMPLETED_QTY] = CurQty and DateTime.Date([DUE DATE]) = CurDate and [LAST TOUCHED] < CurTouch) [QTY])][res]As AlexisOlson mentioned, if it is slow, especially if you have lots of data, you should look for alternative ways. BUT, still, you can take it as a chance to learn, a step in M can be a table, a list, a parameter...etc
I've sent you my email, feel free if you need more help.
8 Replies
Hi Villeminl
if I understand correctly, you want to do something like this
= Table.AddColumn(#"Changed Type", "Custom", each [CurID = [PART_ID], CurOp=[COUNT_REL_OPERATION], CurQty=[COMPLETED_QTY], res=List.Sum( Table.SelectRows(#"Changed Type", each [PART_ID]=CurID and [COUNT_REL_OPERATION] =CurOp and [COMPLETED_QTY] > CurQty) [QTY])][res])Thanks for your input.
I tried it and it worked. It actually created a new comlumn as an imbedded table that I can expend.
Once I expend it duplicates all the rows from my original table so I decided to move that new customer column to a new query (right click; Add as new query) so I can then combine both queries and avoid those duplicates.
Although I noticed that when expeding the table, some fields (custom added columns) were missing, I tried to tweak the code you gave me to add some extra calculated fields and it resulted in an error as the fields couldn't be found.
Would you know why some custom columns added in the original query are disapearing from the results of the table coming from your code?
Here is what I'm trying and where I'm getting an error:
= Table.AddColumn(#"Changed Type", "SUMIF3", each [CurID = [PART_ID],
CurOp=[COUNT_REL_OPERATION],
CurQty=[COMPLETED_QTY],
CurDate=[DUE DATE],
CurTouched=[LAST TOUCHED],
res=List.Sum(Table.SelectRows(#"Changed Type", each [PART_ID]=CurID and [COUNT_REL_OPERATION] =CurOp and [COMPLETED_QTY] =CurQty and [DUE DATE] = CurDate and [LAST TOUCHED] < CurTouched)
[QTY])][res]))
"Expression.Error: The field 'LAST TOUCHED' of the record wasn't found."
[LAST TOUCHED] is a custom column that was previously added in my main Query.
This custom column is calculated using fields from 3 queries that I merged in my main Query,
Not sure if this has an incidence...
Hi Villeminl
My bad, you pasted all the code (I did from the formula bar) to the column, it should be like this, it is a column, not a table...
[CurID = [PART_ID], CurOp=[COUNT_REL_OPERATION], CurQty=[COMPLETED_QTY], res=List.Sum( Table.SelectRows(#"Changed Type", each [PART_ID]=CurID and [COUNT_REL_OPERATION] =CurOp and [COMPLETED_QTY] > CurQty) [QTY])][res]
