Create a column with results based on multiple rows per a grouping ID

[Taxman]

This is fairly simple in Excel, which I have more experience with, but I want to make it work in Power Query (using the interface or M code).

I have a dataset with multiple products in Col A.  Each product can have any number of rows (hence multiple instances of the product ID).  I have a column that says "FOUND" or "NOT FOUND" for each and every row.  I want to create a new column that says "FOUND" on all rows for a given product if ANY of the rows in Col B say "FOUND."  Conversely, I want that new column to say "NOT FOUND" if ALL rows of Col B, for any given product, say "NOT FOUND"

Product ID (Col A)	FOUND (Col B)	DESIRED COLUMN
111	FOUND	FOUND
111	NOT FOUND	FOUND
111	NOT FOUND	FOUND
222	NOT FOUND	NOT FOUND
222	NOT FOUND	NOT FOUND
333	FOUND	FOUND
333	NOT FOUND	FOUND
333	FOUND	FOUND
333	NOT FOUND	FOUND

I first attempted to Group by the Product ID and I attempted various permutations of trying two grouping levels with Products and the "FOUND" column, without any success.  I thought that I might be able to group by product with a column that counts the number of "FOUND" results per Product (which would at least tell me that any product that has "0" is a "NOT FOUND").  However, I couldn't get there and maybe my entire approach was wrong.

Any help would be greatly appreciated.

Thank you!

P.S. Just for reference, the Excel formula to do this similar thing is: =IF(COUNTIFS(A:A,A2,B:B,"FOUND")>0,"FOUND","NOT FOUND")

[wdx223_Daniel]

each a{[Product ID=[Product ID]]}[n]

works like the Excel function of VLOOKUP

it gets the value from "a", which product id equals to the product id of current row.

[wdx223_Daniel · Accepted solution]

=let a=Table.Group(PreviousStepName,"Product ID",{"n",each if List.Contains([FOUND],"FOUND") then "FOUND" else "NOT FOUND"}) in Table.AddColumn(PreviousStepName,"NewCol",each a{[Product ID=[Product ID]]}[n])

[Taxman]

Wow, thank you!  This did work!  I will definitely mark this as the answer, but before I do, can I ask if you happen to know if what this code does is doable just using the interface (without using M)?  I'm sure this further betrays my lack of experience with PQ.  I kept wanting to do somehting akin "For Each" Grouped ID.  Is that what is happening in your code at the "each a" part?

Thank you so much, once agian.