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
Fowmy's avatar
Fowmy
Super User
5 years ago

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]),",")

 

 

 



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"

     

     

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

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

       

       

      Thank you for your time and help, 

       

      Denisse

      • Fowmy's avatar
        Fowmy
        Super User
        5 years ago

        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.