cancel
Showing results for 
Search instead for 
Did you mean: 
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 IV
Super User IV

@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
Solution Sage
Solution Sage

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
Solution Sage
Solution Sage

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
Solution Sage
Solution Sage

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 IV
Super User IV

@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

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors