How to combine two rows based on condition?

[Anonymous]

I need assistance in combining two rows from a table based on a condition. Here is a sample table of my current data:

ID	Manager ID
A123	A456
A123	A678
B456	A678
C789	A678
B123	A789

If an ID has two manager IDs, "A456" and "A678", then I want to only keep the row with "A456." Otherwise keep the record as normal. This is what I want to table to look like:

ID	Manager ID
A123	A456
B456	A678
C789	A678
B123	A789

[Anonymous]

@Anonymous - Is there something special about A456? What is the criteria that causes that one to be selected?

[fhill]

Are you trying to hard code each ID / Manager combination, or is there some Logic or Pattern to use in the selection?

[Anonymous]

@fhill

@natelpeterson

I think the answer is I want to hardcode. There are only two manager IDs that this applies to. Basically, if an ID does not belong to these two specific manager IDs, then their ID will not be duplicated in the table.

So I want a table that results from this logic: if an ID has manager ID "A456" AND "A678" THEN keep "A456" OTHERWISE, no change.

[fhill]

See the other reply if you want something more technical.  Here's a simple measure if you are trying to just make something small and easy.

New Measure on the Table:

Single_Mgr_Measure = IF(

                      COUNTA(Table3[ID]) = 1,  -- If there's only 1 Mgr Id..

                      MIN(Table3[Mgr ID]),        -- Return just that 1 Mgr ID

                                IF(

                                 CONTAINS(Table3,Table3[Mgr ID],   -- Else, If MGR ID CONTAINS 

                                      "A456"),"A456","Other Mgr"))      -- "A456" Return "A456", else "Other Mgr".

You can clean up the code as much as you need, and even inbed further IF Statements if you need to check for further combinations of mgrs.  Here's my final result.  I added BOB twice to the table with QQQ & RRR as Mgr IDs.

ID	Single_Mgr_Measure
A123	A456
B123	A789
B456	A678
Bob	Other Mgr
C789	A678

[Anonymous · Accepted solution]

@Anonymous  - You could do something like this in PowerQuery:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjQ0MlbSUXI0MTVTitVB8M3MLcB8J5AEEt/Z3MISRR6qHiQcGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, #"Manager ID" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Manager ID", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"MIN Manager ID", each List.Min([Manager ID]), type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Grouped Rows",{{"MIN Manager ID", "Manager ID"}})
in
    #"Renamed Columns"

I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.

[Anonymous]

I think the answer is I want to hardcode. There are only two manager IDs that this applies to. Basically, if an ID does not belong to these two specific manager IDs, then their ID will not be duplicated in the table.

So I want a table that results from this logic: if an ID has manager ID "A456" AND "A678" THEN keep "A456" OTHERWISE, no change.