Calculate Maximum and Minimum Classifications by ID in Excel Power Query
Question details
Calculate the maximum and minimum classification for each ID while retaining the original obligation, ID, and classification columns.

- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- Obligation records and their classifications are stored in separate tables and need to be combined and summarized by ID.
- Observed behavior
- Each original row should show its classification along with the maximum and minimum classifications for its ID.
Check that both tables have matching obligation-number columns and that the classification values use the intended order. If classifications are text labels with a business-specific hierarchy, assign numeric ranks before calculating the maximum and minimum.
Merge the tables and add classification summaries by ID
Use this approach to calculate an ID-level maximum and minimum without losing the original obligation rows.
In Excel, select each source table and choose Data > From Table/Range to open it in Power Query. Check that the obligation-number columns in both queries have the same data type.
In the obligation query, select Home > Merge Queries. Choose the classification table, select the obligation-number column in each table, and use a Left Outer join. Expand the merged column and select Classification.
In the Queries pane, right-click the merged obligation query and select Reference. In the new query, select Home > Group By, choose ID, and switch to Advanced. Add a Maximum aggregation and a Minimum aggregation, both using the Classification column.
Right-click the merged obligation query again and select Reference. In this new query, select Home > Merge Queries, choose the grouped summary query, and match the ID columns with a Left Outer join.
Select the expand button on the merged summary column, choose the maximum and minimum columns, and select OK. Confirm that the obligation, ID, and classification columns remain, then choose Home > Close & Load.

Work with Classification Tables in WPS Spreadsheets
WPS Office is a free, lightweight alternative for working with spreadsheet data in a familiar interface. Open Excel files in WPS Spreadsheets for an easy transition, though it does not fix Excel Power Query itself.

Frequently Asked Questions
Why do I need to merge the grouped results back into the original data?
Group By produces one summary row per ID. Merging that summary into a reference of the obligation query adds the maximum and minimum to each original row.
What if my classifications are labels rather than numbers?
Assign a numeric rank that reflects the intended hierarchy before using Group By. Calculate the maximum and minimum ranks, then map them back to labels if needed.
Why did the merge create extra rows?
The classification table may contain more than one matching row for an obligation number. Check the obligation-number column in that table and resolve unwanted duplicates before expanding the merge.
What happens if an obligation number has no match?
With a Left Outer join, the obligation row remains, but its expanded Classification value is null. Check that the key values and data types match in both queries.




