Forum Discussion
SUMIFS functionality using Power Query M advanced editor
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.
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]
This looks like a good translation of SUMIFS as was requested and should work nicely on small datasets.
Villeminl If this approach is too slow on your particular dataset, you may wish to ask a separate question about how to achieve your end goal efficiently rather than how to replicate a specific function.
