Forum Discussion

jcymclean1's avatar
jcymclean1
Frequent Visitor
4 years ago
Solved

Keeping Specific Duplicates in Table

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!

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

     

3 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      jcymclean1
      Frequent Visitor
      4 years ago

      Thank you so much! 

       

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