Remove duplicates based on another column value

[cheezy]

hi

struggling with how to remove duplicate entries in this scenario:

ID	Status	Comment	Test
1	Validated	blah	one
1	Unvalidated		one
2	Unvalidated	blah	two
3	Validated		one
4	Unvalidated	blah	two
4	Validated	blah	two

what i would like to achieve is this:  

ID	Status	Comment	Test
1	Validated	blah	one
2	Unvalidated	blah	two
3	Validated		one
4	Validated	blah	two

so if a record has same ID then delete the record that has an Unvalidated status 

any help appreciated

thanks

[cheezy]

many thanks this worked a treat

[mdaatifraza5556 · Accepted solution]

Hi @cheezy 

You can create a calculated table using DAX the below to get your result.

CleanedTable =

VAR ValidatedIDs =

    SUMMARIZE(

        FILTER('Table','Table'[Status] = "Validated"),

        'Table'[ID]

    )

RETURN

    FILTER(

        'Table',

        'Table'[Status] = "Validated" ||

        (

            'Table'[Status] = "Unvalidated" &&

            NOT 'Table'[ID] IN ValidatedIDs

        )

    )

If this answers your questions, kindly accept it as a solution and give kudos.