Forum Discussion

SantoshKumar's avatar
SantoshKumar
Microsoft Employee
4 years ago
Solved

How to remove duplicate IDs based on applying condition on another Column

Hi Team,

Datawithexpectation

I have below sample table in which i want to remove duplicate ID values based on Status Column ( it has to remove duplicates if exists in ID and it should be of "Non-Complaint" only)

 Data here:

IDStateStatus
A1234ActiveCompliant
A1234ActiveNon-Compliant
B1235ActiveCompliant
B1235ActiveNon-Compliant
C12347ActiveCompliant
D12348ActiveNon-Compliant
D12348DeactiveCompliant
Q12340DeactiveCompliant
C12342ActiveNon-Compliant
Z21234DeactiveCompliant

 

i have sample file but i am not able to see any option available to upload it.

 

Thanks

Santosh

General Comment
Need Help
Show and Tell
Tips and Tricks
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    Hi, SantoshKumar 

     

    You can try the following methods.

    Column =
IF (
    CALCULATE (
        COUNT ( 'Table'[ID] ),
        FILTER ( 'Table', [ID] = EARLIER ( 'Table'[ID] ) )
    ) > 1
        && [Status] = "Non-Compliant",
    2,
    1
)

    Add the new column to Filters and set it equal to 1.

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    SantoshKumar , Try a new table like

     

    filter(addcolumns(distinct(Table), "_1", countx(filter(Table, Table[ID] = earlier(Table[ID])),[ID])), [_1] >1 && [status] ="Non-Complaint")

    • SantoshKumar's avatar
      SantoshKumar
      Microsoft Employee
      4 years ago

      amitchandak  i am able to create a new table which provides duplicate data as per my condition. 
      i have a request here like 1) can we create a new column in the same table instead of new table with value of only [_1] which will have the values such as 1, 2 etc where 1 is value which i am looking out and will exclude values greater than 1 when i want to show the table eliminating duplicate values. can we achieve it? please guide me in this requirement.

       

      Thanks

      Santosh

      • v-zhangti's avatar
        v-zhangti
        Community Support
        4 years ago

        Hi, SantoshKumar 

         

        You can try the following methods.

        Column =
IF (
    CALCULATE (
        COUNT ( 'Table'[ID] ),
        FILTER ( 'Table', [ID] = EARLIER ( 'Table'[ID] ) )
    ) > 1
        && [Status] = "Non-Compliant",
    2,
    1
)

        Add the new column to Filters and set it equal to 1.

        Is this the result you expect?

         

        Best Regards,

        Community Support Team _Charlotte

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.