Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 B12...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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.
    Nathan Peterson

     

fhill's avatar
fhill
Icon for Resident Rockstar rankResident Rockstar
6 years ago

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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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.