Power Query - Managing log of changes with import from Folder

[pratafran]

Hi forum!

I have the following problem.

I have a daily excel file with Tracking information for my shipments (let's simplify it as a database with Shipment ID and Estimated Time of Arrival Date) 

I'm importing this folder and making an append of each version of the file to keep record of the log of changes of ETA for my shipments

Then I apply a Remove duplicates (based on Shipm ID and ETA) to remove those days in which there are no ETA changes (just to minimize the database size) and keeping always the last record for duplicates (with the Buffer tip). So in the example above, the Mar/29 and Apr/01 versions are omitted from the final version with this result:

Sometimes the changes of ETA are much more so my final database can have 10 ETA changes and I'm only interested in keeping a record of the First ETA reported, the Last one and the previous one to the most recent one to have a metric of ETA change vs previous one. Of course the result is dynamic and depends on the day and the quantity of files that exist.

For the example above, this would be the result I expect (remember that this is a Folder Import with a daily excel file).

 

Is there a way you imagine I can achieve this result?

Thanks in advance!

[Greg_Deckler]

I believe that you want to first Sort your data on date and then remove duplicates? I seem to recall that there is also a Table.Buffer step that you want in these kinds of situations or the sorting doesn't "stick". @ImkeF or @edhans can probably be more specific.