Power Query Problems

Keep the Latest Excel File Version per Date with Power Query

Rana GarciaRana Garcia Nov 2, 2026 869 views

Question details

The user wants to import multiple versioned Excel files for the same dates from a folder using Power Query, filtering out older files to keep only the highest version per date while preserving the 'Content' column.

How to Keep the Latest File Version per Date in Power Query
Product
Excel
Device & OS
not provided
Scenario
Importing a folder of continuously updated Excel files where multiple versions exist for single dates, and extracting data only from the newest versions.
Observed behavior
Power Query imports all files leading to duplicate data, or the necessary 'Content' column is lost when attempting to use the Group By function to find the maximum version.
Before you start

Ensure your file metadata or file names contain clearly identifiable Date and Version indicators that Power Query can split into distinct columns.

Solution 1Recommended

Sort, Buffer, and Remove Duplicates

By explicitly sorting the data, buffering it into memory, and then removing duplicates based on the Date column, you ensure that the highest version is retained while keeping the Content column intact for later extraction.

A common issue in Power Query is that operations like 'Remove Duplicates' can behave unpredictably if the query engine reorders steps for optimization. Wrapping your sorted table in the Table.Buffer() function forces the exact order into memory before duplicates are removed.

Using Group By often drops unaggregated columns (like Content). The buffer and deduplicate method bypasses this limitation entirely.

1
Sort the Columns

Click the drop-down arrow on your 'Date' column and select 'Sort Ascending'. Then, click the drop-down on your 'Version' column and select 'Sort Descending'.

2
Buffer the Sorted Table

Open the Advanced Editor from the Home tab (or edit the formula bar directly). Wrap your sort step with the Table.Buffer function. It should look like this: = Table.Buffer(Table.Sort(#"Previous Step Name",{{"Date", Order.Ascending}, {"Version", Order.Descending}}))

3
Remove Duplicates

Right-click the 'Date' column header and select 'Remove Duplicates'. Because the table is buffered in memory, Power Query will keep the first instance it sees (which is your highest version) and remove all older versions for that date.

4
Extract the Content

Click the double-arrow expand icon on the 'Content' column to combine the binaries and extract the data specifically from the filtered, latest files.

Sort, Buffer, and Remove Duplicates
Why Table.Buffer Works: Table.Buffer loads the table into memory in its current state. This stops Power Query's lazy evaluation from ignoring your sorting step when you subsequently remove duplicates.
Free Microsoft Office alternative

Need a Fast, Lightweight Tool for Daily Data Tasks? Try WPS Office

If navigating complex M-code and Power Query transformations feels overwhelming for simple data deduplication tasks, consider WPS Office. It provides an intuitive, highly compatible spreadsheet environment that easily handles filtering, sorting, and deduplication without heavy system requirements.

  1. 1. Download and Install WPS Office: Visit the official WPS website to download the free installation package and follow the on-screen prompts.
  2. 2. Open Your Excel Files: Launch WPS Spreadsheet and seamlessly open your existing .xlsx or .csv files with zero formatting loss.
  3. 3. Easily Deduplicate Data: Highlight your data range, navigate to the Data tab, and click 'Remove Duplicates' to clean your dataset in seconds.
100% compatible with Microsoft Excel (.xlsx, .xls, .csv) formatsLightweight architecture ensures fast loading and smooth performance on older machinesBuilt-in Data tab with intuitive 'Remove Duplicates' and 'Advanced Filter' toolsFamiliar user interface makes migration seamless and requires no retraining
microsoft office alternative - wps office

Frequently Asked Questions

Why does the 'Group By' function remove the Content column?

When you use the Group By feature, Power Query essentially builds a new summary table. It only keeps the columns you group by and the new aggregate columns you specify (like Max Version). Any other columns, such as 'Content', are discarded unless you specifically aggregate them using the 'All Rows' operation.

Do I always need to use Table.Buffer when removing duplicates?

Yes, if the outcome depends on a specific sorted order. Power Query uses lazy evaluation, meaning it tries to optimize queries and might ignore previous sort steps if it thinks they are unnecessary for removing duplicates. Table.Buffer locks the sorted data in memory so the deduplication process strictly follows your intended order.

How can I separate the Date and Version from my file names before sorting?

Before sorting, use the 'Split Column' feature on the 'Name' column. You can split by delimiter (such as an underscore or dash) or use 'Extract > Text Between Delimiters' under the Transform tab to isolate the Date and Version into their own dedicated columns.