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:  ID Medication 1 ...
Need Help
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"