The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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
@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]),",")
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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"
⭕ 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
@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.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group