How to Keep the Most Recent Record per User in Power Query
Question details
The user needs to filter a dataset to keep only the most recent record per user, retaining associated data like the room assigned on the latest date.
- Product
- Microsoft Excel (Power Query)
- Device & OS
- not provided
- Scenario
- Handling a dataset where individual users have multiple entries across different dates, and only the latest status or room assignment needs to be reported.
- Observed behavior
- The goal state is to group the records by user and return the exact row corresponding to the maximum date value for each unique user, eliminating all older entries.
Ensure your dataset is successfully loaded into the Power Query Editor and verify that your date column is formatted as a Date or Date/Time data type so the maximum value can be calculated properly.
Use Group By and the Table.Max Function
This method groups your dataset by the user identifier and utilizes a custom M formula to extract the entire row associated with the most recent date.
By grouping all rows for each user into a nested table, you can apply the Table.Max function to accurately pinpoint the latest record without losing any adjacent column data, such as room numbers.
Select your User column, navigate to the Home tab on the Power Query ribbon, and click 'Group By'. In the dialog box, set the New column name to 'All' and choose 'All Rows' as the operation. Click OK.
Navigate to the Add Column tab and click 'Custom Column'. Give your new column a descriptive name (e.g., 'LatestRecord') and enter the formula =Table.Max([All],"Date"). Ensure you replace "Date" with the exact name of your date column.
Right-click the grouped 'All' column and remove it to prevent data duplication. Then, click the expand icon (two diverging arrows) on your new 'LatestRecord' column header to extract the room, date, and any other required fields.
Need a Lightweight Alternative for Data Analysis?
While Power Query is a dedicated feature of Microsoft Excel, WPS Office provides a lightweight, highly compatible alternative for everyday data processing. With advanced Pivot Tables, built-in functions like MAXIFS, and robust data filtering tools, WPS Spreadsheet handles complex data summarization without heavy system resource usage.
Frequently Asked Questions
Can I keep the most recent record without writing custom M code?
Yes. You can achieve this by first sorting the Date column in descending order (Newest to Oldest). Then, select the User column, right-click, and choose 'Remove Duplicates'. Because Power Query retains the first instance it encounters, it will naturally keep the row with the most recent date.
Why does the Table.Max function return an error?
This typically happens if the column referenced in the formula is not formatted as a Date/Time data type, or if there is a typographical error in the column name. Verify your spelling and ensure the target column has the calendar icon in its header indicating a date type.
How do I group by multiple criteria before finding the latest record?
Hold the Ctrl key and select multiple columns (e.g., User and Department) before clicking the 'Group By' button. Power Query will group the dataset by unique combinations of both fields, allowing you to apply the Table.Max function to each specific grouping.
