Forum Discussion
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!!
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"
No, the first step was just for me to create some example data.
You onnly need to add the second step to your query:
13 Replies
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 RemovedDuplicatesHI 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!
No, the first step was just for me to create some example data.
You onnly need to add the second step to your query:
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.
Thanks a lot!!
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?
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 RemovedDuplicatesfor sorting choose Order.Ascending or Order.Descending.
This should work
That is perfect!!
Thank you very much Anonymous!
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:
I try to remove dublicate for "Contributing" column but I give that error. How can fix that.
Thank you.
