Forum Discussion
Remove duplicates only if the duplicate values contain a certain text
Dear community,
I have a column with a list of store articles, duplicates are alowed, except for one Article.
I would like to remove duplicates only when the duplicate values contain "WRS".
Is it a way to modify the remove duplicates code to do this?
Thank you,
N
6 Replies
OK, I would still do it in two steps--still, not added column or duplicate table. Pretend your current final step is named FinalStep. Add a new step, name it Step1:
= Table.Distinct(Table.SelectRows(FinalStep, each Text.Contains([Article list], "WRS")))
Then add another new step, named Step2:
= Table.Combine(Table.SelectRows(FinalStep, each not Text.Contains(Article list], "WRS"), Step1)
That should do it!--Nate
That should be it!
--Nate
If the above don't solve your problem, maybe provide a sample table.
Paul Zheng _ Community Support Team
Dear Watkinnc,
Thank you for the solution, it is working, however I am trying to avoid duplicating columns or tables because I have a database that has almost 700.000 records and it is getting slower and slower with each modification like this. I am looking for a solution that doesent need anly column addition or query duplicaiton. I need also to take in consideration an another column.
The original data looks like this:
StoresArticle
Store1 Article1 Store1 Article1 Store1 Article1 Store1 Article2 Store1 Article3 Store1 Article3 Store1 Article4 WRS Store1 Article4 WRS Store1 Article5 WRS Store1 Article5 WRS Store2 Article1 Store2 Article1 Store2 Article1 Store2 Article2 Store2 Article3 Store2 Article3 Store2 Article4 WRS Store2 Article4 WRS Store2 Article5 WRS Store2 Article5 WRS And I need it transformed to this:
StoresArticle
Store1 Article1 Store1 Article1 Store1 Article1 Store1 Article2 Store1 Article3 Store1 Article3 Store1 Article4 WRS Store1 Article5 WRS Store2 Article1 Store2 Article1 Store2 Article1 Store2 Article2 Store2 Article3 Store2 Article3 Store2 Article4 WRS Store2 Article5 WRS I would need a code to add it in one step without addin and the removing columns or duplicationg queries.
Saple file: Dropbox link to sample file
Thank you,
You could also duplicate the table, name it WRS, and then filter for just the values that end with WRS--then remove duplicates. Then filter out the rows where Names ends with "WRS". Then combine both tables as a new query.
So in your duplicated table, add this in the formula bar:
= Table.Distinct(Table.SelectRows(PriorStepName, each Text.Contains([Names], "WRS")))
Then, in your original table, add this in the formula bar:
Table.SelectRows(PriorStepName, each not Text.Contains([Name], "WRS"))
Then you can combine both tables as a new query with the combine function in the GUI.
--Nate
