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


Subscribe and learn Power BI from these videos

Website 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


Subscribe and learn Power BI from these videos

Website 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


Subscribe and learn Power BI from these videos

Website 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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

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.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors