Keep the Latest Excel File Version per Date with Power Query
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.

- 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.
Ensure your file metadata or file names contain clearly identifiable Date and Version indicators that Power Query can split into distinct columns.
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.
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'.
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}}))
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.
Click the double-arrow expand icon on the 'Content' column to combine the binaries and extract the data specifically from the filtered, latest files.

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. Download and Install WPS Office: Visit the official WPS website to download the free installation package and follow the on-screen prompts.
- 2. Open Your Excel Files: Launch WPS Spreadsheet and seamlessly open your existing .xlsx or .csv files with zero formatting loss.
- 3. Easily Deduplicate Data: Highlight your data range, navigate to the Data tab, and click 'Remove Duplicates' to clean your dataset in seconds.

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.




