Remove duplicates

[M_BAKOUR_95]

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 !

[v-alq-msft · Accepted solution]

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.

[Anonymous]

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

[amitchandak]

@M_BAKOUR_95 

You have an option under transform data/ edit queries

https://community.powerbi.com/t5/Desktop/How-to-remove-duplicate-records/td-p/128875

or try

new Table = distinct(Table)

[M_BAKOUR_95]

@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]

@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])