Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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
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
You should be able to do it like so:
= Table.Distinct(NameOfPriorStep, {{"Name", Text.Contains(_, "WRS")}})
--Nate
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,
If the above don't solve your problem, maybe provide a sample table.
Paul Zheng _ Community Support Team
@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
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.