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
Nandor
Helper I
Helper I

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

1 ACCEPTED SOLUTION
Fowmy
Super User
Super User

@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

Fowmy_0-1625641701362.png


Output

Fowmy_1-1625641721286.png

 

 

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

6 REPLIES 6
watkinnc
Super User
Super User

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


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
watkinnc
Super User
Super User

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


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
watkinnc
Super User
Super User

You should be able to do it like so:

 

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

 

--Nate


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

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

Store1Article1
Store1Article1
Store1Article1
Store1Article2
Store1Article3
Store1Article3
Store1Article4 WRS
Store1Article4 WRS
Store1Article5 WRS
Store1Article5 WRS
Store2Article1
Store2Article1
Store2Article1
Store2Article2
Store2Article3
Store2Article3
Store2Article4 WRS
Store2Article4 WRS
Store2Article5 WRS
Store2Article5 WRS

And I need it transformed to this:

StoresArticle

Store1Article1
Store1Article1
Store1Article1
Store1Article2
Store1Article3
Store1Article3
Store1Article4 WRS
Store1Article5 WRS
Store2Article1
Store2Article1
Store2Article1
Store2Article2
Store2Article3
Store2Article3
Store2Article4 WRS
Store2Article5 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,

 

 

 

V-pazhen-msft
Community Support
Community Support

@Nandor 

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

 

Paul Zheng _ Community Support Team

Fowmy
Super User
Super User

@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

Fowmy_0-1625641701362.png


Output

Fowmy_1-1625641721286.png

 

 

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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.

Top Solution Authors
Top Kudoed Authors