Help with duplicates rows

[Anonymous]

Good morning friends,

My database looks like the one below.
If the "name" column has some duplicates, like for example Luca's 3 rows and Tom's 2, I need to leave only 1 row for each. And the row that I need to leave is the one with the column "situation" = work.

Can you help me?

NAME	ROLE	Unity	Situation	Fired date
Jhon	fighter	Team 11	Fired	10/04/2022
Luca	apprentice	Migration	Fired	05/02/2021
Luca	fighter	Team 12	Work
Luca	trainee	Migration	Fired	05/03/2021
Joseph	driver	Team 11	Fired	06/07/2021
Tom	apprentice	Migration	Fired	06/08/2021
Tom	driver	Team 14	Work
Amanda	fighter	Team 13	Work

[Vera_33 · Accepted solution]

Hi @Anonymous 

For duplicated rows, you have only 1 row with [Situation]="Work"? Added a custom column to check the condition and filter based on it, paste the M code in Advanced Editor via a blank query to have a look

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY9PC8IwDMW/ivQsNO3mn6sXD0NvAw9jh7LFLcjaEauf3yjIRlHxlJeQ33u8qlJFH7xaqjN1fUQWVaIbFsaI2hNjK9OAhlxbsFbVy0odbo2TqxtHRh+pQVmO1LGL9LJ6Y7DSYJ+YmWNpkBV1CnyRMX+L7MjjL+tssi7CFcde7i3T/UsJWGvYTEgZhr86CLVNqSQlTxrsBufbT1Wz+WP9AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [NAME = _t, ROLE = _t, Unity = _t, Situation = _t, #"Fired date" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom",  (x)=>[a= List.Count( List.Select(Source[NAME],each _=x[NAME])),
b=if a=1 then 1 else if x[Situation] = "Work" then 1 else 0][b]),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1))
in
    #"Filtered Rows"