cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
barbforsman Regular Visitor
Regular Visitor

Remove duplicate rows in column A based on newest date in column B

I have a table with duplicates in Column A.  I want to remove the duplicates based on the date in column B, keeping the most recent record.  I've sorted by = Table.Sort(#"Reordered Columns1",{{"Column A",Order.Ascending},{"Start Date", Order.Descending}}) so the most recent record is listed first.  

Capture.JPGTable with duplicates

But when I remove duplicates in column A in the next step after sorting, it is not necessarily keeping the newest record.

Capture2.JPG

 

I've looked at other posts along these lines and some people were able to use Group By, while others talked about buffering the table before removing the duplicates. How should I remove duplicates so that the newest record remains?

TIA

1 ACCEPTED SOLUTION

Accepted Solutions
Nick_M New Contributor
New Contributor

Re: Remove duplicate rows in column A based on newest date in column B

Probaby a couple ways to handle this, but here's what I did:

  1. Sorted the original table by Date, Order Descending
  2. Grouped By your first column ( what I called [Name] ) and aggregate by all rows:
  3. GroupBy.png
  4. Added a custom column to keep the first row of each sub table
Table.FirstN( [AllDate],1)
  • Remove all the other columns and expand the remaining table
  • Final Table.png

Here's the pbix file:

https://1drv.ms/u/s!Amqd8ArUSwDS0neWaxRugrgvRmnk

2 REPLIES 2
Nick_M New Contributor
New Contributor

Re: Remove duplicate rows in column A based on newest date in column B

Probaby a couple ways to handle this, but here's what I did:

  1. Sorted the original table by Date, Order Descending
  2. Grouped By your first column ( what I called [Name] ) and aggregate by all rows:
  3. GroupBy.png
  4. Added a custom column to keep the first row of each sub table
Table.FirstN( [AllDate],1)
  • Remove all the other columns and expand the remaining table
  • Final Table.png

Here's the pbix file:

https://1drv.ms/u/s!Amqd8ArUSwDS0neWaxRugrgvRmnk

barbforsman Regular Visitor
Regular Visitor

Re: Remove duplicate rows in column A based on newest date in column B

Thank you so much for walking me through it!  Appreciated immensely!