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.
Yes, [LAST TOUCHED] is column in this query. It's custom column added during the previous steps
For some reasons I'm getting this error message where the field 'LAST TOUCHED' of the record is not being found,
I'm doing this:
= [CurID = [PART_ID],
CurOp=[COUNT_REL_OPERATION],
CurQty=[COMPLETED_QTY],
CurDate=[DUE DATE],
CurTouch=[LAST TOUCHED],
res=List.Sum(
Table.SelectRows(#"Changed Type", 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]
and the result:
Expression.Error: The field 'LAST TOUCHED' of the record wasn't found
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.