Remove duplicates from union and can select which column values should retain

[phil_galvz]

Seeking help regarding the union functions. I wanted to remove the duplicates from Active List table with the same employee ID from Separated List table and retain the value from Separated List table.

Current results:

Employee ID	Employee Name	Status
101004262	Jon Doe	Active
101004262	Jon Doe	Separated

Should be result:

Employee ID	Employee Name	Status
101004262	Jon Doe	Separated

This is my code:

UNION(

    SELECTCOLUMNS('ACTIVE LIST',"Employee ID",'ACTIVE LIST'[ID No.],"Employee Name",'ACTIVE LIST'[Employee Name],"Status",'ACTIVE LIST'[Status]),

SELECTCOLUMNS('SEPARATED LIST',"Employee ID",'SEPARATED LIST'[ID No.],"Employee Name",'SEPARATED LIST'[Employee Name],"Status",'SEPARATED LIST'[Status]))

Appreciate your help.

[v-weiyan1-msft]

Hi @phil_galvz ,

If your problem is still not resolved, can you share sample data and the results you are hoping for? Show it as a screenshot or excel. Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,
Yulia Yan

[amitchandak]

@phil_galvz , Try like

Summarize(
UNION(
SELECTCOLUMNS('ACTIVE LIST',"Employee ID",'ACTIVE LIST'[ID No.],"Employee Name",'ACTIVE LIST'[Employee Name],"Status",'ACTIVE LIST'[Status]),
SELECTCOLUMNS('SEPARATED LIST',"Employee ID",'SEPARATED LIST'[ID No.],"Employee Name",'SEPARATED LIST'[Employee Name],"Status",'SEPARATED LIST'[Status])), [Employee ID], [Employee Name], "Status", max([Status]))

[phil_galvz]

Hi Amit,

Tried your suggestions but I have errors encountered.