Forum Discussion

Yue's avatar
Yue
Frequent Visitor
8 years ago
Solved

Remove duplicate texts in one cell

Hi, I am currently shaping my data. I have one column called "Keywords Used in Last Week". However, there are so many duplicate texts in just one cell. How Can I remove the duplicate texts and only show distinct texts? Anyone can help on this?Thanks!!

 

 

For example: How can "app proxy" in row 3 in first column only be shown once in that cell? So in that cell texts could be:

 

"app proxy, maf policies, mfa, spo, sspr,mfa nps"

  • MarcelBeug's avatar
    MarcelBeug
    8 years ago

    No, the first step was just for me to create some example data.

     

    You onnly need to add the second step to your query:

13 Replies

  • MarcelBeug's avatar
    MarcelBeug
    Icon for Community Champion rankCommunity Champion
    8 years ago

    Just split the text on commas, trim the results, take distinct values and combine the result back:

     

    let
    Source = #table(type table[Keywords Used In Last Week = text],
                {{"No Keywords Used, No Keywords Used, No Keywords Used"},
                 {"app proxy, maf policies, mfa, app proxy, spo, app proxy, sspr, app proxy, mfa nps"}}),
    RemovedDuplicates = Table.TransformColumns(Source,{{"Keywords Used In Last Week", each Text.Combine(List.Distinct(List.Transform(Text.Split(_,","), Text.Trim)),", ")}})
in
    RemovedDuplicates
    • Yue's avatar
      Yue
      Frequent Visitor
      8 years ago

      HI Marcel,

       

      Thanks for the answer. However, I want to remove all duplicates in column " Keywords Used in Last Week". so the column should look like:

       

      Center and asap, center, asap, service account ,code review, code, sso,........     59

      deployment, No Keywords Used, template policies, how do you......                  16

      ..................

      training, yammer                                                                                                  3

      ............

      No Keywords Used                                                                                               1.

       

      How can I remove the duplicate texts in the whole column at the same time? Do I need to write down all texts in each row from this column as you did in the code?

      Sorry, I am very new to M code, could you please also show me more detailed steps on where to write this code in powerbi? I would really appreciate your help! Thanks!

      • MarcelBeug's avatar
        MarcelBeug
        Icon for Community Champion rankCommunity Champion
        8 years ago

        No, the first step was just for me to create some example data.

         

        You onnly need to add the second step to your query:

    • JoeGearhart's avatar
      JoeGearhart
      Regular Visitor
      7 years ago

      This is great! I had an identical issue where one semi-colon delimitted cell contained a number of duplicates. Your solution worked perfectly for me to clean this up. Thank you.

  • caruso1058's avatar
    caruso1058
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    MarcelBeug, This is awesome and it worked perfectly for me. However, my output has 20 instances in some cases. 

    Is there a way using M to Sort the output Alphebitically?

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Yep. It can be sorted alphabetically. Try

       

      let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    RemovedDuplicates = Table.TransformColumns(Source,{{"Custom - Copy", each Text.Combine(List.Sort(List.Distinct(List.Transform(Text.Split(_," "), Text.Trim)),Order.Descending),", ")}})
in
    RemovedDuplicates

       for sorting choose Order.Ascending or Order.Descending.

       

      This should work

      • caruso1058's avatar
        caruso1058
        Icon for Microsoft Employee rankMicrosoft Employee
        6 years ago

        That is perfect!!

         

        Thank you very much Anonymous!

  • BrianaHop's avatar
    BrianaHop
    Icon for Helper I rankHelper I
    5 years ago

    Is anyone having success using this with a MySql Connection? I adjusted the M Code to use my MySQL db as a source and "field_cx_dd_log_cdinitial_req" as the column to dedup. But when I save it, it gives me an error that it can't find the column. See below images for reference: 

     

     

  • Serdarhs's avatar
    Serdarhs
    New Member
    3 years ago

    I try to remove dublicate for "Contributing" column but I give that error. How can fix that.

    Thank you.