Combine Duplicate Rows and Sum Values in Excel Power Query
Question details
The user needs to combine duplicate asset rows and sum their values using Power Query across XLSX and CSV files.
- Product
- Excel Power Query
- Device & OS
- not provided
- Scenario
- Cleaning similar asset records from XLSX and CSV files, normalizing matching keys, and merging the data to sum the 'Hits' column without losing the correct path.
- Observed behavior
- Requires a reliable method to group data by path and asset, sum the values, and handle similar but not identical asset names without using unsafe fixed character limits.
Ensure both your XLSX and CSV data files are saved, closed in other applications, and accessible. It is highly recommended to create a backup of your original files before applying complex Power Query transformations.
Group Data and Sum Values with Normalized Keys in Power Query
This method creates a normalized matching key to group the data accurately, sum the 'Hits', and merge the queries safely.
By normalizing your text strings first, you prevent minor inconsistencies from creating separate groups. Grouping the CSV data allows you to accurately sum values before merging it back with your primary XLSX query.
Load your XLSX and CSV files into Power Query by opening Excel and navigating to Data > Get Data > From File.
In the CSV query, go to Add Column > Custom Column. Create a normalized key (e.g., using Text.Lower or Text.Trim) to standardize the 'Asset' values.
Select the 'Path' column and your new normalized 'Asset' key column. Right-click the column headers and choose 'Group By'.
In the Group By dialog box, set the New Column Name to 'Total Hits', set the Operation to 'Sum', and choose the 'Hits' column. Click OK.
Select your XLSX query from the left pane, click 'Merge Queries' on the Home tab, and use the 'Path' column to join with your grouped CSV query. Expand the required columns to finalize.
Combine Duplicates and Sum Values in WPS Office
If you need a straightforward way to combine duplicate rows and sum values without writing complex Power Query formulas, WPS Spreadsheet offers an intuitive 'Consolidate' tool that does the job instantly.
- 1. Open your data file: Launch WPS Spreadsheet and open your CSV or XLSX file containing the duplicate rows.
- 2. Access the Consolidate tool: Select an empty cell where you want the combined data to appear, then navigate to the 'Data' tab and click on the 'Consolidate' button.
- 3. Select your function and range: In the Consolidate dialog, select 'Sum' as the function. Click the reference box and highlight the data range containing your duplicate rows and 'Hits' values.
- 4. Combine the data: Check the boxes for 'Top row' and 'Left column' under 'Use labels in', then click 'OK' to instantly merge duplicates and sum their values.
Frequently Asked Questions
How do I avoid combining the wrong assets when merging in Power Query?
When using Fuzzy Matching in the Merge options, you can adjust the Similarity Threshold (a value between 0.00 and 1.00). Setting a higher threshold ensures only highly similar items are merged. Always review the output preview to ensure distinct assets aren't lumped together.
Can I group by multiple columns in Power Query?
Yes. In the 'Group By' dialog, select the 'Advanced' option at the top. This allows you to add multiple grouping levels, such as grouping by both 'Path' and your 'Asset Key', before defining your aggregation operation like summing.
What is the best way to normalize text columns before grouping?
The safest method is to remove excess spaces and convert text to a uniform case. You can select the column, go to the Transform tab, click 'Format', and apply 'Trim' followed by 'Lowercase' or 'Uppercase'. This ensures case mismatches do not create duplicate groups.
Why does my 'Sum' operation return errors in Power Query?
Errors usually occur if the column you are attempting to sum contains text strings, null values, or spaces instead of numerical data. Ensure you change the data type of the 'Hits' column to 'Whole Number' or 'Decimal Number' before performing the Group By operation.
