Find Duplicate Numbers in Column and remove if different value in 2nd column else leave

[justmeok]

This is a little hard to explain in the title subject line but I will try to provide a clear example below. I receive a csv file from our website and then I am writing a power query to extract the data required from this file. We only have two categories and any orders that are ONLY for the second category will to be kept and orders for the first category or a mix of both categories need to be removed completely. See example and under the heading action is the result I expect from the DAX formula.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Category[/TD]
[TD]Order Number[/TD]
[TD]Action Reqd[/TD]
[/TR]
[TR]
[TD]Category1[/TD]
[TD]1460[/TD]
[TD]Remove row[/TD]
[/TR]
[TR]
[TD]Category1[/TD]
[TD]1461[/TD]
[TD]Remove row[/TD]
[/TR]
[TR]
[TD]Category2[/TD]
[TD]1461[/TD]
[TD]Remove row[/TD]
[/TR]
[TR]
[TD]Category2[/TD]
[TD]1462[/TD]
[TD]Keep row[/TD]
[/TR]
[TR]
[TD]Category2[/TD]
[TD]1463[/TD]
[TD]Keep row[/TD]
[/TR]
[TR]
[TD]Category2[/TD]
[TD]1463[/TD]
[TD]Keep row[/TD]
[/TR]
[TR]
[TD]Category2[/TD]
[TD]1463[/TD]
[TD]Keep row[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks everyone hoping this is possible and one of you gurus can help

 

[macfuller]

If I understand your question I would suggest creating a 2nd import of your source data, filtering out Category 2 items, then merging with your original query on the order number. If your merged query has a value in the new column against a Category 2 value that means there was a matching Category 1 order and you don't want the row. Then in the merged query you can filter out all Category 1 values and all not null rows in the new column.

 

[justmeok]

Wow macfuller thank you so much!!! This is such a simple solution and it worked brilliantly Nothing like a bit of lateral thinking - you have saved my sanity. I really really appreciate your prompt reply and assistance.