Forum Discussion
How to remove duplicates using a condition
Hello Power BI Community,
I am trying to figure out couple of things:
1) Either to remove the duplicate rows
OR
2) To avoid them in my count column
I have a dataset where 1 employee can be repeated many times but the task should not be repeated. Please review the data image below.
So I have to achieve the count of 1 for the combination (employee, task).
If there is a way to delete the rows in power Query editor that would be great. Or if there is a way to write DAX count by eliminating the duplicate occurrences, I can use that as well.
help is much appreciated, Thanks in advance! Anonymous Nathaniel_C Mariusz Greg_Deckler
Are you trying to achieve:
Measure = COUNTROWS(DISTINCT(TableName))Can also be accomplished as:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKk7MVdJRSjQ0VIrVgfOMjHDzjI3BvKxMEC8Jqg/Kg6qE8lBVEs8zMUHmmZqCeXmZyfk5qUhORQhAdSMEMLSA3BULAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [employee = _t, task = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"employee", type text}, {"task", type text}}), #"Removed Duplicates" = Table.Distinct(#"Changed Type") in #"Removed Duplicates"with
Measure 2 = COUNTROWS('TableName (2)')
6 Replies
Go to Power Query, Home tab, Remove Rows, Remove Duplicates.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
NathanielHey Nathaniel_C ,
Thanks for the response. I cannot delete the entire duplicates. I can assign the same task to multiple employees but the rule is each employee can have a particular task only once. So, I have to remove the rows where the count >1(excluding the 1st row with the duplicate, since I will lose the original row). But cannot remove all of them.
Hi SriKandimalla ,
Go to Power Query, and try it. You can always reverse it. Remove Duplicates, means after the first one.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
