Forum Discussion

M_BAKOUR_95's avatar
M_BAKOUR_95
New Member
6 years ago
Solved

Remove duplicates

Hello all
I want to remove duplicates from a table and keep the  row with the most recent date.
For example, when the values between the "bnf_name" and "wife_name" columns match on two different rows, I want to delete the row with the oldest date in the "verification date" column and keep the row with the most recent date.
Or filter to the full table based on the previous condition.
In the picture below, lines 1 and 3 are the same so I want to delete line 3 and keep line 1 because the value of the "verification date" column is newer.
Any Help !

 
 



 

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, M_BAKOUR_95 

     

    Based on your description, I created data to reproduce your scenario.

    Table:

     

    You may create a calculated table as below.

     

    Compare = 
ADDCOLUMNS(
    SUMMARIZE(
        'Table',
        'Table'[bnf_name],
        'Table'[Wife_name]
    ),
    "NewDate",CALCULATE(MAX('Table'[Date]))
)

     

     

    Result:

    Compare:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

    • M_BAKOUR_95's avatar
      M_BAKOUR_95
      New Member
      6 years ago

      amitchandak 
      Thanks, but this method does not work
      Because the "compare" table does not appear in the power query and new Table = distinct (Table)
      Also, it does not work because the rest of the values in the rest of the columns are different, and I just want to take the date with the latest date between the matching lines with the name and wife

      • amitchandak's avatar
        amitchandak
        Super User
        6 years ago

        M_BAKOUR_95 , Seem like you have an ID column 

        Max Id = maxx(filter(table, table[Name]=earlier(table[Name]) && table[WIFE]=earlier(table[WIFE])
					&& table[Date]>=earlier(table[Date])),Max(Table[ID]))

         

        Now filter

        new Table = filter(table,table[ID]=table[Max ID])

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    Such things should be performed in Power Query, not in DAX. DAX is a Data Analysis Expressions language, not a data mashup engine.

    Best
    D
  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    Hi, M_BAKOUR_95 

     

    Based on your description, I created data to reproduce your scenario.

    Table:

     

    You may create a calculated table as below.

     

    Compare = 
ADDCOLUMNS(
    SUMMARIZE(
        'Table',
        'Table'[bnf_name],
        'Table'[Wife_name]
    ),
    "NewDate",CALCULATE(MAX('Table'[Date]))
)

     

     

    Result:

    Compare:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.