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! and hit thumbs up


Blog - Data Visualization

Website   YouTube    LinkedIn   PBI User Group

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! and hit thumbs up


Blog - Data Visualization

Website   YouTube    LinkedIn   PBI User Group

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! and hit thumbs up


Blog - Data Visualization

Website   YouTube    LinkedIn   PBI User Group

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
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors