Power Query Problems

Excel Power Query: Create a Consolidation Column from Two Columns

Algirdas JasaitisAlgirdas Jasaitis Nov 12, 2026 868 views

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.
Before you start

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.

Solution 1Recommended

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.

1
Open the Power Query Editor

In Excel, select the source table and choose Data > From Table/Range, or open the existing query through Data > Queries & Connections.

2
Insert a custom column

In the Power Query Editor, select Add Column > Custom Column.

3
Name the new field

In the New column name box, enter a name such as Consolidated.

4
Enter the conditional formula

In the Custom column formula box, enter: if [Col3] = 1 then [Col1] else [Col2]

5
Apply the query

Select OK, review the new Consolidated column, then choose Home > Close & Load to return the result to Excel.

Expected result: Each row uses the value from Col1 when Col3 equals 1. Every other row uses the value from Col2.
Free Microsoft Office alternative

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. 1. Open the workbook: Launch WPS Spreadsheets and select Open to load the Excel XLSX file containing Col1, Col2, and Col3.
  2. 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. 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. 4. Save the compatible workbook: Choose Menu > Save As, select Excel 2007-365 Workbook (*.xlsx), and save the consolidated result.
Create a consolidation column with a familiar IF formula in WPS Spreadsheets.Open and save Microsoft Excel XLSX workbooks with broad format compatibility.Use a lightweight spreadsheet editor without the overhead of a full Power Query workflow.Copy the completed formula-based result back into an Excel workbook when needed.
microsoft office alternative - wps office

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.