Power Query Create conditional new column

Question

I am having a little challenge. In the attached sample data you can see that for a single AMI two different Product names exist.I do know that when that occurs only Linux/UNIX is correct. I would like to create a new column that will be looking for each AMI if it has 2 distinct string values then the new column to always be Linux/UNIX.

İmage

For the time being, i am using in power query text.contains and i specify manually those AMIs but this is not so productive. I am not aware if i can run loops with power query.

Thanks in advance for your help. Filippos

Answer 1

Group the data on Ami and count Product names; merge results back into table; add custom column that checks the new column like =if [newcolumn] >1 then "Linux" else null

Another possibility. Sort table. Click to select product and type column then right click remove duplicates, which should only keep first instance. If you sorted correctly, then all the non Linuxes will be removed