Excel Power Query: Create a Consolidation Column from Two Columns
Question details
The user needs to create one consolidation column that selects a value from Column 1 or Column 2 according to the value in Column 3.
- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- A Power Query table contains two possible source columns and a condition column. The user wants to choose the value from Column 1 when Column 3 equals 1; otherwise, use the value from Column 2.
- Observed behavior
- The desired consolidation field has not yet been created, and the user may be considering DAX functions such as EARLIER even though the calculation uses values from the current row.
Open the table in the Power Query Editor and confirm the headers are exactly Col1, Col2, and Col3, including capitalization and spaces. Check that Col3 contains the expected numeric value 1 and that Col1 and Col2 have compatible data types.
Add a Power Query custom column with an IF expression
Use this method when the selection rule is simple and you want to enter the complete row-based formula directly.
Power Query evaluates the expression for each row, so the formula can refer directly to [Col1], [Col2], and [Col3]. EARLIER and EARLIEST are DAX functions and are not required for this current-row calculation.
In Excel, select the source table and choose Data > From Table/Range, or open the existing query through Data > Queries & Connections.
In the Power Query Editor, select Add Column > Custom Column.
In the New column name box, enter a name such as Consolidated.
In the Custom column formula box, enter: if [Col3] = 1 then [Col1] else [Col2]
Select OK, review the new Consolidated column, then choose Home > Close & Load to return the result to Excel.
Build the same field with a conditional column
Use this visual method when you prefer selecting the condition and output columns from menus instead of writing the full M expression.
Use WPS Spreadsheets for Formula-Based Column Consolidation
WPS Office is a free, lightweight alternative for consolidating two columns with a row-based IF formula when you do not need Microsoft Power Query. Its familiar spreadsheet interface supports Excel-compatible workbooks, making it easy to open, edit, and migrate XLSX files.
- 1. Open the workbook: Launch WPS Spreadsheets and select Open to load the Excel XLSX file containing Col1, Col2, and Col3.
- 2. Add the consolidation formula: In the first cell of the new column, enter =IF(C2=1,A2,B2), replacing the cell references with the locations of Col3, Col1, and Col2 in your sheet.
- 3. Fill the formula down: Select the formula cell and drag its fill handle down the table, or copy and paste it into the remaining rows.
- 4. Save the compatible workbook: Choose Menu > Save As, select Excel 2007-365 Workbook (*.xlsx), and save the consolidated result.

Frequently Asked Questions
What is the correct Power Query formula for choosing between two columns?
In the Custom Column dialog, enter if [Col3] = 1 then [Col1] else [Col2]. This returns Col1 when Col3 is 1 and Col2 for all other values.
Do I need EARLIER or EARLIEST for this Power Query column?
No. The expression reads values from the current row, so direct column references in an if...then...else expression are sufficient. EARLIER and EARLIEST are associated with DAX row-context calculations, not this Power Query step.
Can I create the consolidation field without writing M code?
Yes. Select Add Column > Conditional Column, set Col3 to equal 1, choose Col1 as the output for a match, and choose Col2 in the Otherwise field.
What happens when Col3 contains a value other than 1?
The else branch is used, so the result comes from Col2. If blank or nonnumeric values need special handling, add additional conditions or clean Col3 before creating the column.
Why does Power Query report an error for the formula?
Check that the column names match the headers exactly and that the formula uses square-bracket references such as [Col3]. Also verify that Col3 has a compatible data type for comparison with the number 1.




