Forum Discussion
How to combine two rows based on condition?
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 - 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.
6 Replies
Are you trying to hard code each ID / Manager combination, or is there some Logic or Pattern to use in the selection?
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.
@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.
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 IDIF(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
