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.
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?