How to Dynamically Clean and Merge Duplicate Columns in Power Query
Question details
The user needs to dynamically clean column headers, remove generic prefixes, and merge duplicate columns from raw exports (like Jira) without hardcoding every specific column name.
- Product
- Excel Power Query
- Device & OS
- not provided
- Scenario
- Processing raw data exports that contain messy, dynamic, or duplicate column headers that need to be consolidated dynamically.
- Observed behavior
- System exports generate multiple duplicate columns with similar data and generic wrapper text (e.g., 'Custom field (FieldName)') that cannot be merged efficiently using static UI steps.
Ensure your raw data export is loaded into Power Query. It is highly recommended to duplicate your original query as a backup before applying advanced M code transformations or structural unpivoting.
Use the Unpivot and Pivot Method to Clean Headers
Unpivoting the data allows you to apply row-level text transformations to your column headers before pivoting them back into a clean, consolidated structure.
This method is highly recommended if you prefer using the Power Query user interface over writing complex M code. By turning columns into rows, you can use standard text extraction tools to clean the headers dynamically.
Select your static identifier columns (such as 'ID', 'Description', or 'Labels'). Right-click one of the selected column headers and choose 'Unpivot Other Columns'. This will create 'Attribute' and 'Value' columns.
Select the new 'Attribute' column. Go to the Transform tab, select 'Split Column' > 'By Delimiter', and use the closing parenthesis ')' as your delimiter. Then, use 'Replace Values' to strip out unwanted prefixes like 'Custom field ('.
Delete any leftover columns generated by the split that contain the removed prefixes or suffixes, keeping only the cleaned 'Attribute' column and the 'Value' column.
Select the cleaned 'Attribute' column. Go to the Transform tab and click 'Pivot Column'. In the dialog box, select 'Value' as the Values Column. In the Advanced options, choose not to aggregate if you are dealing with text, or select the appropriate aggregation.
Use Advanced M Code with List.Accumulate for Dynamic Merging
For advanced users, writing a custom M query using List.Accumulate allows you to dynamically iterate over and merge duplicate column headers without unpivoting large datasets.
Try WPS Office for Your Data Analysis Needs
While advanced Power Query M-scripting is specific to Microsoft Excel, WPS Office provides a lightweight, free alternative that boasts excellent compatibility with Excel formats. It is perfect for handling cleaned datasets, running complex formulas, and creating pivot tables without the burden of a heavy subscription.
- 1. Download and Install: Get WPS Office for free from the official website and install it on your device.
- 2. Open Your Cleaned Dataset: Launch WPS Spreadsheet and open the .xlsx or .csv file containing your exported data.
- 3. Analyze Your Data: Use built-in PivotTables, filters, and advanced functions to extract insights from your consolidated data.
Frequently Asked Questions
Why should I use List.Accumulate to merge duplicate columns?
List.Accumulate allows Power Query to iterate over a dynamic list of column names, merging them automatically without needing you to hardcode each specific column name. This is ideal for system exports where custom field names or quantities change frequently.
Can I achieve dynamic column renaming without writing custom M code?
Yes. Standard UI features in Power Query usually hardcode column names, but by using the Unpivot method, you convert columns into row values. You can then manipulate these headers using standard UI text tools, and Pivot them back, effectively achieving dynamic renaming with minimal coding.
How do I handle null values when combining duplicate columns dynamically?
When merging columns dynamically in M code, you should wrap your column list in 'List.RemoveNulls(_)' before combining the text. This ensures that empty cells are ignored and your merged text doesn't contain consecutive, unnecessary delimiters.
