Forum Discussion

phil_galvz's avatar
phil_galvz
New Member
2 years ago

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

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 IDEmployee NameStatus
101004262Jon DoeActive
101004262Jon DoeSeparated

 

Should be result:

Employee IDEmployee NameStatus
101004262Jon DoeSeparated

 

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.
Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      phil_galvz
      New Member
      2 years ago

      Hi Amit,

       

      Tried your suggestions but I have errors encountered.

  • v-weiyan1-msft's avatar
    v-weiyan1-msft
    Icon for Community Support rankCommunity Support
    2 years ago

    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