Forum Discussion
How to remove duplicate based on one condition
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
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.
3 Replies
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.
Works like a charm. Thank you so much for the help!
You're very welcom, happy to help! Have a great weekend!
