How to remove duplicate based on one condition

[ljx0648]

Hi guys,

I have a rather simple data table structure like below:

ID	Assigned to	Phone Available
1	A	YES
1	A	YES
2	B	NO
2	B	YES
3	C	NO
3	C	NO

I need to remove duplicate but i need to keep record with YES if the record also has a NO (i.g. ID2 with B in the case here)

Desired output:

ID	Assigned to	Phone Available
1	A	YES
2	B	YES
3	C	NO

May I know how to achieve the result above in Power Query? Or if there is any way to remove duplicate after importing from Power Query?

I have tried a method by creating an additioanl column named "sort" when phone column = yes then 1 else 2, after that I sort it ascending and remove duplicate, but the result return for ID 2 is still NO. The reason I believe is the Power Query still sorting from the source using Phone Available then the column I created. 

I have also searched on the forum and google but none of the method works.

Any tips are much appreciated!

Thank you guys

[audreygerred · Accepted solution]

Hi! I have the following data entered:

In Power Query, I sorted Phone descensding so that all the Yes are above all the No. I then added an index column, and then removed duplicates on ID. VIOLA! It keeps the first instance in the list which will be Yes because of the sorting and indexing.

[ljx0648]

Works like a charm. Thank you so much for the help!

[audreygerred]

You're very welcom, happy to help! Have a great weekend!