cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ruesaintdenis
Helper II
Helper II

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

7 REPLIES 7
Fowmy
Super User IV
Super User IV

@ruesaintdenis 

You can add a custom column with the following code 

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

 

Fowmy_0-1619470812945.png

 

 



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn

Hello @Fowmy , 

 

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

@ruesaintdenis 

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"

 

 

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


@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!). 

 

Pwoer bi source.PNGsource.PNG

 

Thank you for your time and help, 

 

Denisse

@ruesaintdenis 

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors