Combine DAM and RTM Values by Date in Excel Power Query
Question details
The user needs to consolidate data by summing numeric columns (DAM and RTM commodity values) so that each date is represented by only one aggregated row.
- Product
- Excel Power Query
- Device & OS
- not provided
- Scenario
- Transforming and cleaning data to summarize numeric commodity values grouped by a specific date column, while discarding the commodity type column.
- Observed behavior
- The dataset currently has multiple rows per date due to different commodity types (DAM and RTM), and the goal state is a single consolidated row per date that sums all numeric metrics.
Ensure your raw data is formatted as an Excel Table and successfully loaded into the Power Query Editor before applying these transformation steps.
Use the Unpivot and Pivot Approach (Dynamic Layout)
This is the recommended dynamic approach in Power Query Editor that will automatically adapt even if new value columns are added to your source data later.
Instead of manually specifying each column to sum, this method removes the commodity Type, unpivots all numeric values, and then pivots them back grouped by Date using the List.Sum aggregation.
In the Power Query Editor, locate the 'Type' column containing your DAM and RTM labels. Right-click the column header and select 'Remove'.
Select the 'Date' column by clicking its header. Right-click the header and choose 'Unpivot Other Columns'. This transforms all your numeric columns into a structured list of 'Attribute' and 'Value' pairs.
Ensure the 'Date' column is explicitly set to the Date data type, and the 'Attribute' column is set to the Text data type using the icon next to the column headers.
Highlight the 'Attribute' column. Navigate to the Transform tab on the ribbon and click 'Pivot Column'. In the dialog box, set 'Value' as the Values Column. Expand Advanced Options and choose 'Sum' as the Aggregate Value Function.
Use the Standard Group By Feature
A straightforward approach using the Power Query ribbon for tables that have a fixed, unchanging number of numeric columns.
Use WPS Spreadsheet as a Free Alternative for Data Analysis
If you are dealing with advanced Microsoft Excel functions and want a lightweight, free alternative for your daily data processing, WPS Office is highly recommended. It offers excellent format compatibility and powerful built-in PivotTable features to achieve complex data aggregation results without writing code.
- 1. Download and Install: Get WPS Office from the official website and install it on your computer.
- 2. Open your Excel File: Launch WPS Spreadsheet and open your existing .xlsx file containing the raw DAM and RTM data.
- 3. Insert a PivotTable: Select your data range, go to the Insert tab, and choose PivotTable. Drag 'Date' to the Rows area and your numeric fields to the Values area to instantly sum them by date.
Frequently Asked Questions
How do I access the Power Query Advanced Editor to paste M code?
In the Power Query Editor, go to the Home tab and click on 'Advanced Editor' located in the Query group. This opens a window where you can directly paste or modify the underlying M code for your data transformation.
Why do I need to remove the Type column before unpivoting?
If you retain the commodity Type column, Power Query will include those distinct DAM and RTM labels in the grouping or unpivoting process. Removing it forces the query to collapse the data strictly by Date during the final pivot.
What happens if I have blank cells in my numeric columns when pivoting?
Power Query's List.Sum function usually handles null values by ignoring them during aggregation. However, to prevent calculation errors across different data types, it is good practice to replace nulls with zeros before executing the pivot.
Can I use the aggregated Power Query data in DAX formulas?
Yes. When loading the data from Power Query, select 'Close & Load To...', and check the box that says 'Add this data to the Data Model'. This loads the grouped table directly into Power Pivot, making it available for DAX time intelligence functions.
