Keeping Specific Duplicates in Table

Hi jcymclean1 ,

Please follow these steps:

1.Click Staff Name column --> Group By -->All Rows:

2.Add a custom column:

=if Table.RowCount([Count])> 1 then Table.SelectRows([Count],each [Status]="Reassign") else [Count]

3.Remove Count column and expand Custom column, the output is shown below:

The whole M syntax:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjFQ0lHyys/IUwjOzSzJAHJQUKwOUIkZkOURpOCSWZSaXJJfhI0XkpqYC2QZArGzqz+Q9Esth2g2B3JcMtMzSxJzFHwT8xLTU4uwigTn55SWZObnKbiklqXm5BfkpuaV4DEWh7NxWBeUmlhcnJmeB9FrAVJooODi6x+M0IhhINiqWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [EmployeeKey = _t, #"Staff Name" = _t, Title = _t, Department = _t, ParentEmployeeKey = _t, #"Report to" = _t, Status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"EmployeeKey", Int64.Type}, {"Staff Name", type text}, {"Title", type text}, {"Department", type text}, {"ParentEmployeeKey", Int64.Type}, {"Report to", type text}, {"Status", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Staff Name"}, {{"Count", each _, type table [EmployeeKey=nullable number, Staff Name=nullable text, Title=nullable text, Department=nullable text, ParentEmployeeKey=nullable number, Report to=nullable text, Status=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each if Table.RowCount([Count])> 1 then Table.SelectRows([Count],each [Status]="Reassign") else [Count]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"EmployeeKey", "Title", "Department", "ParentEmployeeKey", "Report to", "Status"}, {"EmployeeKey", "Title", "Department", "ParentEmployeeKey", "Report to", "Status"})
in
    #"Expanded Custom"

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