Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

New column when IDs repeat

Hello community, 

 

I'm looking to create a new column with a list of unique medications for each ID. 

 

I have a bit of a weird/complicated data, because my IDs repeat: 

IDMedication
1a
1b
1c
2h
3a
3a
3c

 

Some IDs have a row for each medication (ID #1), some IDs have duplicate medications (ID #3) as well as non duplicated medications. 

 

I would like to create a new column with a list of all unique medications for each ID:

IDMedicationMedications
1aa, b, c
1ba, b, c
1ca, b, c
2hh
3aa, c
3aa, c
3ca, c

 

Would anyone know how to create this? 

 

Thank you for taking the time, 

 

Denisse

Need Help

7 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    Anonymous 

    You can add a custom column with the following code 

    (x)=>  Text.Combine( List.Distinct(Table.SelectRows(#"Changed Type", each [ID] =x[ID])[Medication]),",")

     

     

     



    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hello Fowmy , 

       

      Thank you, I tried your code, but I got a function, I'm not sure how to fix this? 

      • Fowmy's avatar
        Fowmy
        Super User
        5 years ago

        Anonymous 

        You need to modify the step names as per your query. Create a new blank query, go to the Advanced Editor, clear the existing code and paste the following then follow the steps.

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUpUitWBsJLgrGQwywjIygCzjOHqUFlAdbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Medication = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Medication", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", (x)=> Text.Combine( List.Distinct(Table.SelectRows(#"Changed Type", each [ID] =x[ID])[Medication]),","))
in
    #"Added Custom"