How to Keep the First Four Qualifying Records per Name in Power Query
Question details
The user wants to use Power Query to extract exactly the four most recent records per name, but only if those four records match a strict positional sequence: blank/null, 1, a number, and another number.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Filtering a large dataset to retain specific groups of data based on chronologically sorted sequential conditions.
- Observed behavior
- The goal state is a cleanly filtered dataset containing only the names (and their four corresponding rows) that successfully passed the sequence validation, effectively discarding non-qualifying groups.
Ensure your raw dataset is formatted as an official Excel Table (press Ctrl+T) so that Power Query can dynamically capture new records when the data updates.
Group, Index, and Validate Sequence in Power Query
Use Power Query's advanced grouping and custom index functions to isolate the top four recent records per name and validate your specific numerical sequence.
This approach natively handles large datasets by sorting and grouping data before applying conditional logic. Adding an index to a grouped table requires a brief modification to the M-code.
Select your table, go to the Data tab, and click 'From Table/Range' to open the Power Query Editor. Sort the 'Name' column in Ascending order and the 'Date' column in Descending order to ensure the most recent records appear first.
Select the 'Name' column, click 'Group By' on the Transform tab. Set the operation to 'All Rows'. Edit the resulting M-code in the formula bar to add a nested Index column using Table.AddIndexColumn. Expand this new column and filter it to only keep rows where the Index is less than or equal to 4.
Group the filtered table again by Name, this time combining the position values into a single string or list. Add a Custom Column with a conditional statement to check if the exact sequence (null, 1, number, number) is met.
Filter the conditional column to keep only the names that evaluated to 'True'. Finally, expand the nested tables to reveal your final set of qualifying rows.
Click 'Close & Load' on the Home tab to output the successfully filtered and validated dataset into a new Excel worksheet.
Use a Reference Query and Merge
Create a secondary reference query to identify qualifying names, then merge it back to the original dataset.
Looking for a Lightweight Excel Alternative?
While advanced M-code scripting and Power Query are specific to Microsoft Excel, WPS Office provides a highly compatible, fast, and free alternative for managing complex spreadsheets and filtering massive datasets.
- 1. Download and Install: Visit the official WPS Office website to download the free installation package for your operating system.
- 2. Open Your Spreadsheet: Launch WPS Spreadsheets and open your existing .xlsx file directly. No file conversion is necessary.
- 3. Manage Your Data: Utilize the comprehensive Data tab to sort, filter, and extract your required records using familiar tools.
Frequently Asked Questions
How do I add an index to grouped rows in Power Query?
To add an index per group, use the 'Group By' feature and select 'All Rows'. Then, modify the generated M-code in the formula bar, wrapping the grouped table in the `Table.AddIndexColumn` function before expanding it.
Why use Power Query over VBA for sorting and filtering 45,000 records?
Power Query provides a visual, step-by-step interface that automatically updates when your source data changes. It efficiently handles 45,000 records in the background without requiring you to write and maintain complex multi-pass VBA macro loops.
Can I use a reference query instead of grouping to filter records?
Yes. You can create a reference to your main query, filter that reference query down to just the target names that meet your positional sequence criteria, and then perform an Inner Join (Merge) back with the original table to extract the final rows.
