Remove duplicates only if the duplicate values contain a certain text

[Nandor]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

You should be able to do it like so:

= Table.Distinct(NameOfPriorStep, {{"Name", Text.Contains(_, "WRS")}})

--Nate

[Nandor]

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,

[Anonymous]

@Nandor 

If the above don't solve your problem, maybe provide a sample table. 

Paul Zheng _ Community Support Team

[Fowmy · Accepted solution]

@Nandor 

You will have to do a different approach. I added a conditional column and an Index column and removed the duplicates. Please check the attached file below my signature.


My Sample Data

Output