Forum Discussion

Yue's avatar
Yue
Frequent Visitor
9 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 s...
  • MarcelBeug's avatar
    MarcelBeug
    9 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:

MarcelBeug's avatar
MarcelBeug
Icon for Community Champion rankCommunity Champion
9 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
9 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
    9 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:

    • Leroynz's avatar
      Leroynz
      Frequent Visitor
      8 years ago

      Hi,

      I have a similar problem. In Power Query Editor I have transformed the table which includes the following column "Custom - Copy". I have shown the last portion of the Advanced Editor M code. I wish to remove duplicate text (e.g. see red box in picture). I have tried altering the code in the advice given to the previous poster but couldn't get it to work. Any advice is appreciated.

      • Leroynz's avatar
        Leroynz
        Frequent Visitor
        8 years ago

        HI,

         

        I solved it. The key was within the "RemovedDuplicates" row of code: the text.split second parameter after the bracket i changed from" , " to " " as my data had a space as separator not a comma separator. Result all duplicates removed. :smileyhappy:.

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