Forum Discussion
Solved
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...
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)')
Hey Nathaniel_C ,
I just tried it, it has left my table with unique values of task.
I need the duplicates as well to be able to assign the same to task to various employees. What I need to get rid of are the ones with duplicated of (Employee & task) combined.
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)')
It solved the problem, Thank you ChrisMendoza
I have a follow-up question. What can we do if we have a unique column such as Row_ID before the employee column. How we will be distinct count then?