How to Group and Merge Rows by Date and Product in Power Query
Question details
The user needs to consolidate multiple data rows by date and product, summing up specific values into a single row per combination.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Organizing and aggregating daily product data from multiple entries into a clean summary table.
- Observed behavior
- The current dataset contains multiple duplicate rows for the same dates and products, which need to be merged and their corresponding numerical values summed up.
Ensure your source data is formatted as an Excel Table and that your date column contains valid date values rather than text to guarantee accurate grouping and sorting.
Use the Advanced Group By Feature in Power Query
This is the most robust method for consolidating rows based on multiple columns (Date and Product) while summing up other numerical columns.
Using the Advanced option in the Group By feature allows you to define multiple grouping levels, ensuring that combinations of dates and products are kept intact rather than forcing a single product per date.
Select your data table in Excel, go to the Data tab on the ribbon, and click 'From Table/Range' to open the Power Query Editor.
On the Home tab or Transform tab within the Power Query Editor, click the 'Group By' button.
In the Group By dialog box, select the 'Advanced' radio button. Select 'Date' for your first grouping, click 'Add grouping', and select 'Product' for the second.
Under the new column names, add aggregations for 'Pieces' and 'Waste'. Choose 'Sum' as the operation from the dropdown and select the respective columns.
Click OK to apply the grouping, reorder or rename your columns if necessary, then click 'Close & Load' on the Home tab to output the summarized data back to Excel.
Create a PivotTable for Quick Aggregation
If you prefer not to use Power Query, a standard PivotTable is a very effective alternative for grouping and summing data by multiple criteria.
Consolidate and Summarize Data Easily with WPS Office
If you are looking for a fast, free alternative to Microsoft Office, WPS Office offers powerful data summarization tools. You can easily group and merge rows by date and product using WPS Spreadsheet's highly compatible PivotTable features without the need for complex queries.
- 1. Open your dataset: Launch WPS Spreadsheet and open your existing Excel workbook.
- 2. Insert a PivotTable: Navigate to the Insert tab, click PivotTable, and select your data range.
- 3. Group your data: Drag Date and Product to the Rows area, and Pieces/Waste to the Values area to instantly merge and summarize the data.
Frequently Asked Questions
Why is my Power Query grouping returning only one product per date?
This happens if you only grouped by the 'Date' column and extracted the first product instead of adding 'Product' as a distinct second grouping level. To fix this, use the 'Advanced' option in the Group By dialog and ensure both Date and Product are added as grouping levels.
Do I have to use Power Query to group and sum rows in Excel?
No, you do not have to use Power Query. Creating a PivotTable is a quick and straightforward alternative for summarizing and grouping data by multiple columns like Date and Product.
How do I update my grouped data when new rows are added?
If you used Power Query or a PivotTable, simply right-click anywhere on the summarized output table and select 'Refresh'. Ensure your original source data is formatted as an Excel Table so new rows are automatically included in the refresh.
Why are my dates not sorting correctly in the final output?
Dates will not sort chronologically if they are stored as text. Check your source data and ensure the date column is properly formatted as Date values, not Text, before running your query or building your PivotTable.
