Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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.
Proud to be a Super User! | |
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.
Proud to be a Super User! | |
Works like a charm. Thank you so much for the help!
You're very welcom, happy to help! Have a great weekend!
Proud to be a Super User! | |
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
70 | |
70 | |
38 | |
28 | |
26 |
User | Count |
---|---|
97 | |
88 | |
59 | |
43 | |
40 |