Replicating a SUMPRODUCT formula using a Query Expression

[mcmuney]

Hi, I have an Excel formula that I'm trying to replicate in a query expression. Can anyone help?

Excel Formula

=IF(SUMPRODUCT(($D$2:$D$12000)*($J$2:$J$12000=J1747))<-999999,"Yes","No")

For the Expression:

ColumnD = B.MONETARY_AMOUNT
ColumnJ = B.ACTIVITY_ID

Basically, my query results will have the ACTIVITY_ID repeat with different AMOUNTS and I need a SUM in a different column. I'm currently doing this in Excel with the Query results, but I'm trying embed this into the query, saving me calc time in Excel.

Is this possible to do?

Thanks.

 

[sandy666]

is that what you want?

Condition = Table.AddColumn(Table.Group(Source, {"B.Activity_ID"}, {{"Sum.Amount", each List.Sum([B.Monetary_Amount]), type number}}), "IF", each if [Sum.Amount] < -999999 then "Yes" else "No")

I assumed you're talking about PowerQuery (Get&Transform)