Remove duplicate texts in one cell

[Yue]

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"

[Serdarhs]

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

Thank you.

[BrianaHop]

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: 

[caruso1058]

@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]

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]

That is perfect!!

Thank you very much @Anonymous!

[MarcelBeug]

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

[benoxie]

Thanks a lot!!

[JoeGearhart]

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.

[Yue]

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 · Accepted solution]

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]

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]

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

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]

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