Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
uc
Helper II
Helper II

Remove duplicates based on value on another column

Below is the sample data

 

IDDOBEthnicityEmail
101/01/2018British or Mixedaa.aol.com
101/01/2018Other Mixed 
202/05/2015British or Mixed British 
202/05/2015 Mixed Britishxx.bt.com
202/05/2015British or Mixed Britishxx.bt.com
301/06/2012Other Mixed

 

ww.bt.com

301/06/2012 

ww.bt.com

401/01/2011White and Asian

 

401/01/2011White and Asian

 

 

I want the output as

IDDOBEthnicityEmail
101/01/2018British or Mixedaa.aol.com
202/05/2015 Mixed Britishxx.bt.com
301/06/2012Other Mixed

 

ww.bt.com

401/01/2011White and Asian 

 

I have a table with duplicates and once I remove the duplicates I want to make sure records that have email addr is not deleted. any advise is greatly appreciated.

1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

Hi @uc ,

 

Multi-select (Ctrl+click) [ID] and [DOB].

On the Home tab, go to Group By.

In the aggregate columns section at the bottom, create two aggregate columns as follows:

 

New column name: Ethnicity

Operation: Max

Column: Ethnicity

 

New column name: Email

Operation: Max

Column: Email

 

This gives me the following output:

BA_Pete_0-1634292294516.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

4 REPLIES 4
BA_Pete
Super User
Super User

Hi @uc ,

 

Multi-select (Ctrl+click) [ID] and [DOB].

On the Home tab, go to Group By.

In the aggregate columns section at the bottom, create two aggregate columns as follows:

 

New column name: Ethnicity

Operation: Max

Column: Ethnicity

 

New column name: Email

Operation: Max

Column: Email

 

This gives me the following output:

BA_Pete_0-1634292294516.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




hi, my table is created by selected columns and union, so i cant use PQ, do you know any other solution for the same problem?

Hi @martaTr, could you provide sample data in usable format (read note below my post if you don't know how) and expected result based on sample data?


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

It works!! Thanks a lot 🙂

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors