Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Yue
Frequent Visitor

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

 

 

Capture.PNG

Capture.PNG

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"

1 ACCEPTED SOLUTION
MarcelBeug
Community Champion
Community Champion

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

 

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

Specializing in Power Query Formula Language (M)

View solution in original post

13 REPLIES 13
Serdarhs
New Member

1.PNG2.PNG

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

Thank you.

BrianaHop
Helper I
Helper I

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: deduperror.jpg

 

 

caruso1058
Employee
Employee

@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
Not applicable

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

That is perfect!!

 

Thank you very much @Anonymous!

MarcelBeug
Community Champion
Community Champion

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
Specializing in Power Query Formula Language (M)

Thanks a lot!!

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.

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?

Capture.PNG

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
Community Champion
Community Champion

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

 

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

Specializing in Power Query Formula Language (M)

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.

image.pngimage.png

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

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.