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

[barbforsman]

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.  

Table with duplicates

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

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

[Anonymous · Accepted solution]

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. 
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
• 

Here's the pbix file:

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

[Anonymous]

Hi,

Here is also a link explaining the solution using Table.Buffer: https://www.youtube.com/watch?v=S3X_HK7yl1w&ab_channel=PragmaticWorks.

It worked well for my case.

BR

[Anonymous · Accepted solution]

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. 
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
• 

Here's the pbix file:

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

[barbforsman]

@Anonymous is there any possiblity you could upload the pbix file again?  Unfortunately in a transition I lost the file and really like to keep it for reference.

Many thanks

[Anonymous]

@barbforsman 

Doesnt the link above still work?

[barbforsman]

I get this message when I navigate to it:

[Anonymous · Accepted solution]

sorry about that. can you see if this one works

https://1drv.ms/u/s!Amqd8ArUSwDS0neWaxRugrgvRmnk?e=hdiOH9

[TheSweeper]

do you mind resharing your pbix file please?  

[MuniqueCrespo]

hello, would you happen to have this PBIX file available that I can have it as well? I am running into a little problem at the end that I am not sure if I did anything wrong. The file would be much appreciated. Thanks a lot

[Ashish_Mathur]

Hi,

Share your data and show the expected result.

[barbforsman]

Oh gosh, thank you!!  This is an invaluable example!

[barbforsman]

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