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:

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
  • GoRo2010's avatar
    GoRo2010
    New Member
    3 years ago

    I was trying this over a few days, and this worked flawless. Thank You