Forum Discussion

SriKandimalla's avatar
SriKandimalla
Helper I
6 years ago
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...
Need Help
  • ChrisMendoza's avatar
    ChrisMendoza
    6 years ago

    SriKandimalla -

    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)')

     

Nathaniel_C's avatar
Nathaniel_C
Community Champion
6 years ago

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

SriKandimalla's avatar
SriKandimalla
Helper I
6 years ago

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. 

  • ChrisMendoza's avatar
    ChrisMendoza
    Resident Rockstar
    6 years ago

    SriKandimalla -

    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)')

     

    • SriKandimalla's avatar
      SriKandimalla
      Helper I
      6 years ago

      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?