Remove rows in Power Query based on duplicates in one column and a specific value in another

[onedayover]

Hi All

I have a table that has duplication in the rows with the exception of 1 column. I've tried to remove certain rows by maniplating some examples which I found but am having trouble trying to get the end result I need.

What I'm trying to do in power query is to remove the entire row if there are any duplicates in the "itemid" column and there is also a "D" in the "Status" column. Here is a before and after look of what I'm trying to do...

Would anyone know if or how I can achieve this?

Thanks

onedayover

[MahyarTF · Accepted solution]

Hi,

This is my solution based on the sample data :

1- I created the Duplicated columns for the ItemId and Status

2- Merge created columns (Copy-Itemid and Copy-Status)

3- Sort rows Ascending on the Merged column :

4- Select the Columns (as below) and remove the duplicated records :

Also, you could delete the merged column

Appreciate your Kudos and please mark it as a solution if it helps you.

[onedayover]

Works perfectly, thank you MahyarTF, your help is much appreciated 🙂

[MahyarTF]

Hi @onedayover 

Glad to help any time