Dax or Power Query (M) Formula for duplicate values in the same column

[lbrown]

If anyone could help me write a dax or power query formula for the two steps below I would greatly appreciate your knowledge.

1. If Column A has a duplicate value, and the value in Column B is the same, then return the record with the most recent date.

2. If Column A has a duplicate value, and the value in Column B is NOT the same, then keep both records.

I'm trying to achieve one formula that can account for both requirements. I've attached images of sample data to better explain. 

[v-yuezhe-msft · Accepted solution]

@lbrown,

Could you please explain that why you have duplicate values for first row? I make a test using the following sample table instead, and create a measure in the table.

maxdate = MAX(Table[Date])

Regards,
Lydia

[lbrown]

@v-yuezhe-msft

I figured out the error. I was adding the table date column instead of maxdate. Your answer worked perfectly, thank you!

[lbrown]

@v-yuezhe-msft

There are duplicates because an entry can be made on the spreadsheet on different days. I am needing to select the most recent date if Column B is the same. If Column B is different then I need to keep both rows.

After creating the measure I am still seeing duplicates.