Remove Dups, but keep the one with latest Modified Date

Plukey

Board Regular
Joined
Apr 19, 2019
Messages
138
Office Version
  1. 2016
Platform
  1. Windows
I have two Column's in Power Query & I'm trying to remove the dups & keep the one with the latest date.
NameDate modified
H4332707Y04-S0110/26/2018
H4332707Y03-S0110/26/2019
H4332707Y04-T0110/26/2018
H4139712B01-T0110/23/2018
H4139712L70-S0110/23/2018
H4139712L70-T0110/23/2018
H4139712B01-S0110/23/2018
H4130701K91-T0110/23/2018
H4130701K91-S0110/23/2018
H4131291Y13-S0110/23/2018
H4171103Y27-T0210/23/2018
H4171103Y26-S0110/23/2018
H4171103Y26-T0210/23/2018
H4171103Y27-T0110/23/2018
H4171103Y27-S0110/23/2020
H4171103Y26-T0110/23/2018
H4171103Y26-T0110/24/2021
H4334006Y72-T0110/24/2018
H4139802K92-S0110/23/2018
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Click fx button and insert the following - make sure to change tbl to the appropriate (usually previous) step name for your query

= Table.Group(tbl, {"Name"}, {{"Date modified", each List.Max([Date modified])}})
 
Upvote 0
Solution
Upvote 0
Power Query creates a set of instructions on how to process a table. The problem is that it doesn't load the entire table before completing the instructions. For small tables, this doesn't matter. But if you have a table of 10 m rows and you set an instruction "keep the latest date", but Power Query processes 1m rows at a time, then chances are it will not keep the latest date. The latest date may be buried in rows between 1m and 10m. What Table Buffer does is force Power Query to load all of the table into memory first before completing the next step, hence eliminates this issue. Normally it is more efficient not to load the entire table first, but in some cases, like this one, it can be important.

Now that I have said that, I am not 100% if Table.Group actually has this problem - I suspect not. The way I did it in my article was to sort the column and then keep the latest date. This approach does have the problem that can be solved by Table.Buffer. I suspect that Table.Group doesn't encounter this problem, and probably gives the correct answer without Table.Buffer
 
Upvote 0

Forum statistics

Threads
1,216,082
Messages
6,128,717
Members
449,464
Latest member
againofsoul

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top