How to Group Items and Sum Quantities with Power Query
Question details
The user needs to combine duplicate item entries in a dataset and calculate the total sum of their quantities using Power Query.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Cleaning and summarizing a dataset where the same item appears multiple times with varying quantities, requiring a consolidated view with total amounts.
- Observed behavior
- The dataset currently lists multiple rows for the same item. The goal is to generate a clean table where each unique item is listed only once with its corresponding total quantity.
Ensure your raw data is formatted as an Excel Table (Ctrl+T) before importing it into Power Query. This ensures that any future rows added to your dataset will be automatically included when you refresh the query.
Use the Group By Feature in Power Query
The most direct way to consolidate duplicate items and sum their quantities is to use the built-in 'Group By' function within the Power Query Editor.
The Group By function works similarly to a Pivot Table, allowing you to aggregate data based on unique values in a specific column. By selecting 'Sum' as the operation, Power Query handles the math and deduplication simultaneously.
Select any cell inside your data table, navigate to the 'Data' tab on the Excel ribbon, and click 'From Table/Range' to open the Power Query Editor.
In the Power Query Editor, locate the 'Item' column (or the column containing your duplicate names). Right-click the column header and select 'Group By' from the context menu.
In the Group By dialog box, under 'New column name', type a title for your results (e.g., 'Total Quantity'). Under 'Operation', click the drop-down menu and select 'Sum'.
Under the 'Column' drop-down, choose the 'Quantity' column (the column containing the numbers you want to add together). Click 'OK' to apply the changes.
Review the newly summarized data in the preview window. If it looks correct, click 'Close & Load' in the top-left corner of the Home tab to output the summarized table into a new worksheet.
Easily Summarize Data with WPS Office
While Power Query is a powerful tool in Microsoft Excel, WPS Spreadsheet offers highly intuitive Pivot Tables and Data Consolidation features that allow you to group items and sum quantities in seconds—without needing to navigate complex query editors. Try WPS Office as a free, lightweight alternative for all your data analysis tasks.
Frequently Asked Questions
Can I group by multiple columns at the same time in Power Query?
Yes. When you open the Group By dialog box, select the 'Advanced' option at the top instead of 'Basic'. This allows you to click 'Add grouping' to group by multiple criteria (such as Item and Date) before applying the sum operation.
Why is the Sum operation resulting in an error or grayed out?
The Sum operation requires numerical data. If your Quantity column is currently formatted as Text, Power Query cannot calculate a sum. You must change the data type by clicking the icon next to the 'Quantity' column header and selecting 'Whole Number' or 'Decimal Number' before grouping.
Is there a way to keep the other columns when grouping?
By default, grouping removes columns that aren't part of the grouping or aggregation. To keep other data, you must use the 'Advanced' Group By settings to either add those columns to the grouping criteria or add 'All Rows' as an aggregation operation to extract additional details later.
