Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
barbforsman
Resolver I
Resolver I

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.  

Table with duplicatesTable 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

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

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

View solution in original post

Anonymous
Not applicable

11 REPLIES 11
Anonymous
Not applicable

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
Not applicable

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

@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
Not applicable

@barbforsman 

Doesnt the link above still work?

I get this message when I navigate to it:

Capture.JPG

Anonymous
Not applicable

sorry about that. can you see if this one works

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

do you mind resharing your pbix file please?  

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

Hi,

Share your data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.