Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Below is the sample data
ID | DOB | Ethnicity | |
1 | 01/01/2018 | British or Mixed | aa.aol.com |
1 | 01/01/2018 | Other Mixed | |
2 | 02/05/2015 | British or Mixed British | |
2 | 02/05/2015 | Mixed British | xx.bt.com |
2 | 02/05/2015 | British or Mixed British | xx.bt.com |
3 | 01/06/2012 | Other Mixed |
ww.bt.com |
3 | 01/06/2012 | ww.bt.com | |
4 | 01/01/2011 | White and Asian |
|
4 | 01/01/2011 | White and Asian |
|
I want the output as
ID | DOB | Ethnicity | |
1 | 01/01/2018 | British or Mixed | aa.aol.com |
2 | 02/05/2015 | Mixed British | xx.bt.com |
3 | 01/06/2012 | Other Mixed |
ww.bt.com |
4 | 01/01/2011 | White 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.
Solved! Go to Solution.
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:
Pete
Proud to be a Datanaut!
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:
Pete
Proud to be a Datanaut!
It works!! Thanks a lot 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.