New column when IDs repeat

[Anonymous]

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: 

ID	Medication
1	a
1	b
1	c
2	h
3	a
3	a
3	c

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:

ID	Medication	Medications
1	a	a, b, c
1	b	a, b, c
1	c	a, b, c
2	h	h
3	a	a, c
3	a	a, c
3	c	a, c

Would anyone know how to create this? 

Thank you for taking the time, 

Denisse

[Fowmy]

@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]

Hello @Fowmy , 

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

[Fowmy]

@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"

[mahoneypat]

You can adapt it as follows.  Put this formula in the custom column pop up box, replace Source with last/previous step.

let thisid = _[ID] in Text.Combine( List.Distinct(Table.SelectRows(Source, each [ID] =thisid)[Medication]),",")

Pat

[Anonymous]

@mahoneypat @Fowmy , 

My source is an excel with multiple sheets, which I load as below, and then expand so it's all continously in one table. Is this this issue? I can't seem to get it to work, and sadly I am not following how to address the issue (still new to this!). 

Thank you for your time and help, 

Denisse

[Fowmy]

@Anonymous 

Based on your question and the sample you provided, solutions were suggested. You can share your Excel file or create a sample file that represents your actual data and show the expected results to understand your data and the expected result.

You can save your file in a cloud space like OneDrive and share the link here.

[Anonymous]

Hello @Fowmy , 

Thank you, I have tried to upload it here, does it work? 

Thank you for your help, I really appreciate it. 

Kind regards, 

Denisse