SOLVED. Here's what I did: I made a new query that filtered out blanks on column A and only show items with "1" in column V. Then I grouped by columns C, L, M & U and made a count of distinct rows. I then merged this query back to my main one.
I have a countifs formula that looks like this, which repeats for each row in a column and the criteria is always the current row item.
=IF(A2="","",COUNTIFS($C$2:$C$1568,C2,$L$2:$L$1568,L2,$M$2:$M$1568,M2,$U$2:$U$1568,U2,$V$2:$V$1568,1))
Translation: If A2 is blank, leave the formula cell blank. Otherwise, count any items that match the current cell values in columns C, L, M and U but only if V = 1.
How can I do this in Power Query? I'm aware of the grouping function, but I can't seem to figure out how to implement. My brain is not working well today!
Thank you
Bookmarks