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 s...
  • 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:

MarcelBeug's avatar
MarcelBeug
Community 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
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.