How to Combine Power Query Tables and Consolidate Duplicate Columns in Excel
Question details
The user wants to append multiple tables in Power Query and merge the resulting duplicate columns that share the same data context (e.g., DOB and DOB1) into a single column.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Appending datasets with slightly different column headers, which creates duplicate columns containing mutually exclusive data.
- Observed behavior
- The appended table generates separate columns for the same data attribute, scattering the data across multiple columns with null values filling the gaps.
Before consolidating your duplicated columns, ensure that both columns are set to the exact same Data Type (e.g., Date) to avoid errors during the merge process.
Consolidate Columns using the Built-in Merge Feature
Use the built-in Merge Columns tool to quickly combine duplicate columns into one when values are mutually exclusive across rows.
This method is ideal when each row only contains a value in one of the duplicated columns, meaning the data is mutually exclusive. Power Query will safely merge the non-null values.
In the Power Query Editor, hold down the 'Ctrl' key and click the headers of the columns you want to consolidate (for example, DOB and DOB1).
Navigate to the 'Transform' tab (to replace the existing columns) or the 'Add Column' tab (to keep the originals and create a new one), then click on 'Merge Columns'.
In the pop-up window, choose a separator if necessary (select 'None' if you are just replacing nulls) and type a name for the new consolidated column.
Select the newly merged column, click the data type icon next to the header, and set the Data Type back to 'Date'.
Consolidate Columns using a Custom Conditional Column
Write a simple conditional formula to prioritize one column's value over another, offering more control than a standard merge.
Edit Spreadsheets and Analyze Data Seamlessly with WPS Office
While advanced M-code and Power Query environments are specific to Microsoft Excel, WPS Office provides a highly compatible, lightweight, and completely free alternative for managing spreadsheets, consolidating data, and handling complex formulas.
- 1. Download the software: Visit the official WPS Office website and download the free installation package.
- 2. Install and launch: Run the installer and follow the on-screen prompts. Once installed, launch WPS Spreadsheet.
- 3. Open your files: Directly open your existing Excel workbooks to start editing, analyzing, and formatting your data instantly.
Frequently Asked Questions
Why does appending tables create duplicate columns in Power Query?
This happens when the tables being appended have slightly different column headers (e.g., 'DOB' in Table A and 'DOB1' in Table B). Power Query strictly matches exact header names; if they differ, it creates a separate column for each unique header.
Can I merge more than two duplicated columns at once?
Yes. You can select multiple columns by holding down the Ctrl key and clicking each header. When you use the Merge Columns feature, Power Query will merge the non-null values across all the selected columns into one.
What happens if both columns have a value in the exact same row when merging?
If you use the standard 'Merge Columns' feature, Power Query will concatenate both values together as text. If you want to prioritize one value over the other instead of concatenating them, it is better to use the Custom Column approach with an 'if...then...else' formula.
