Keeping Specific Duplicates in Table

[jcymclean1]

New to Power Bi Community! Hope i have done this correctly.

I built a dynamic org chart in Power Bi (using Akvelon Visual). It worked wonderfully - but now i have been asked to be able to compare with FUTURE NEEDS.

The following is a table which has the EMPLOYEES and who they report to:

Basically, i want to remove the duplicate STAFF NAME - but keep the REASSIGNED person. 

There are no dates, as i have seen solutions using time... 

Thank you!

[Anonymous · Accepted solution]

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.

[jcymclean1]

Thank you so much! 

Appreciate your efforts - can get frustrating when new and you hit a road block!

[lbendlin]

Group the table by maximum status and then ungroup again.

Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services.