SUMIFS functionality using Power Query M advanced editor

[Villeminl]

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])

[Vera_33]

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])

[Villeminl]

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

[Vera_33]

Hi @Villeminl 

What do you mean by "This custom column is calculated using fields from 3 queries that I merged in my main Query"? You are now in the query - step #"Changed Type", is [LAST TOUCHED] not a column in this query?

[Villeminl]

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

[Vera_33 · Accepted solution]

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.

[Vera_33]

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]

[AlexisOlson]

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.

[Villeminl]

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?