How to Keep One Row per Unique ID Using Power Query in Excel
Question details
The user needs to filter an Excel dataset to retain exactly one record for each unique ID (WKN), specifically keeping the row containing the latest date or the highest ID value along with its related data.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Cleaning up an Excel dataset that contains multiple records per unique ID to find the most recent or highest-value entry per category.
- Observed behavior
- The dataset contains duplicate entries per unique ID, and the objective is to reduce the data to a single, most recent row per ID while maintaining columns like threshold and coupon payment values.
Ensure your dataset is formatted as an Excel Table and that you have a clearly defined column (such as Date or ID) to determine which unique row takes priority.
Sort Data and Remove Duplicates in Power Query
This method relies on Power Query's sorting and duplicate removal tools to easily retain the latest record for each unique identifier by keeping the first encountered row.
Power Query removes duplicates by keeping the top-most row of a given identifier. By sorting the data properly beforehand, you can ensure that the 'top' row is the one with the latest date or highest ID.
Select any cell inside your dataset, navigate to the Data tab on the Excel ribbon, and click 'From Table/Range' to open the Power Query Editor.
Click the drop-down arrow on your unique ID column (e.g., WKN) and select 'Sort Ascending'. Next, click the drop-down on your Date or ID column and select 'Sort Descending' so the latest records appear first.
To prevent Power Query from optimizing out the sort step, open the Advanced Editor and wrap your sorted step with 'Table.Buffer()'. For example: Table.Buffer(SortedRowsStep).
Select the unique ID column header (WKN), right-click it, and choose 'Remove Duplicates'. The query will retain only the first instance (the most recent one) for each unique ID.
Click 'Close & Load' on the Home tab to export your cleaned, deduplicated dataset back into a new Excel spreadsheet.
Group by Maximum Value and Merge Tables
This method is highly robust for complex datasets, ensuring the exact maximum row is identified and then merged back to fetch all related unaggregated columns.
Handle Data Duplicates Easily with WPS Office
While advanced Power Query syntax is specific to Excel, WPS Office provides excellent built-in spreadsheet features to sort data, remove duplicates, and manage large datasets efficiently without complex coding. It is a highly capable and free alternative to Microsoft Office.
- 1. Download and Install WPS Office: Visit the official WPS website to download the free, lightweight WPS Office suite to your device.
- 2. Open Your Excel File: Launch WPS Spreadsheet and open your existing dataset seamlessly without any format loss.
- 3. Easily Clean Your Data: Navigate to the Data tab and use the intuitive 'Remove Duplicates' tool to quickly filter out duplicate rows and keep your data organized.
Frequently Asked Questions
Can I use DAX instead of Power Query to keep one row per unique ID?
Yes. In Power Pivot or Power BI, you can use DAX functions like TOPN or CALCULATETABLE combined with MAX to filter a table and return the latest row per ID. However, using Power Query is generally recommended for shaping and cleaning the data before it enters your data model.
Why didn't 'Remove Duplicates' keep the latest date row in Power Query?
Power Query's 'Remove Duplicates' function retains the first row it encounters. If your data wasn't explicitly sorted in descending order by date prior to deduplication, or if the sorting wasn't locked in using Table.Buffer(), the query engine might process rows unpredictably, resulting in the wrong row being saved.
How do I retain unaggregated columns when grouping in Power Query?
When you use the 'Group By' function to find a maximum date or ID, standard behavior drops unaggregated columns. To retain them, you must merge your grouped summary table back with the original dataset, joining them on both the Unique ID and the Max Date columns.
Is Power Query available in WPS Office?
Currently, the advanced M formula language specific to Power Query is native to Microsoft Excel. However, WPS Office Spreadsheet includes powerful native data tools, Pivot Tables, and built-in duplicate removal functions that handle most standard data cleanup tasks flawlessly.
