Power Query Problems

Merge Excel Power Query Sources and Use a Backup Cost

Elise WilliamsElise Williams Nov 18, 2026 869 views

Question details

Combine two cost sources by item code, using the secondary cost when the primary cost is missing, zero, or unavailable.

Merge Excel Power Query Sources and Use a Backup Cost
Product
Microsoft Excel Power Query
Device & OS
not provided
Scenario
Two spreadsheets contain item codes and costs, and the user wants one preferred cost per primary-table item.
Observed behavior
The desired result uses the primary cost when valid and falls back to the secondary cost otherwise.
Before you start

Check that both sources have matching item-code columns and that costs can be converted to numbers. Check for duplicate item codes in the secondary source, because they can create multiple rows after the merge.

Solution 1Recommended

Merge the cost queries and select the backup cost

Use a left outer merge to retain the primary table's items and choose the secondary cost when needed.

1
Load both sources

In Excel, use Data > Get Data > From File > From Workbook to import each source, or select an existing worksheet table and choose Data > From Table/Range. Open both queries in Power Query Editor.

2
Prepare the columns

In Power Query Editor, set both Item Code columns to the same data type. Set the cost columns to a numeric type, then select Primary Cost and use Transform > Replace Errors to replace errors with null.

3
Merge by item code

Select the primary query and choose Home > Merge Queries. Select the secondary query, click the Item Code column in each table, choose Left Outer as the join kind, and click OK.

4
Expand the backup cost

Click the expand button on the merged column, select the secondary cost field, and click OK. Rename the expanded column Secondary Cost if necessary.

5
Choose the final cost

Choose Add Column > Custom Column, name it Final Cost, and enter: if [Primary Cost] = null or [Primary Cost] = 0 then [Secondary Cost] else [Primary Cost]. Click OK.

6
Load the result

Remove any columns you no longer need, check the Final Cost values, and choose Home > Close & Load to return the result to Excel.

Merge the cost queries and select the backup cost
Unmatched and duplicate codes: A left outer join retains primary-table rows but does not add items found only in the secondary table. An unmatched secondary cost remains null, and duplicate secondary item codes can produce multiple result rows.
WPS Spreadsheets

Use a backup cost formula in WPS Spreadsheets

Use a formula to look up a secondary cost by item code when the primary cost is blank, zero, or an error. This produces the same cost-selection result without using Excel Power Query.

  1. 1. Open the cost workbook: Open the workbook in WPS Spreadsheets. Place primary item codes and costs in columns A and B of one sheet, and secondary codes and costs in columns A and B of a sheet named Sheet2.
  2. 2. Add a result column: In the primary sheet, enter Final Cost in cell C1 and select cell C2.
  3. 3. Enter the fallback formula: Enter =IFERROR(IF(OR(B2=0,B2=""),IFERROR(VLOOKUP(A2,Sheet2!$A$2:$B$1000,2,FALSE),""),B2),IFERROR(VLOOKUP(A2,Sheet2!$A$2:$B$1000,2,FALSE),"")) and press Enter. Adjust the lookup range to cover the secondary data.
  4. 4. Fill and check: Drag C2's fill handle down through the primary items. Check blank results for missing secondary matches, and use File > Save As to save the workbook.
Select a backup cost by matching item codesOpen and save Microsoft Excel XLSX filesUse familiar spreadsheet formulas in a lightweight interface
microsoft office alternative - wps office

Frequently Asked Questions

Why does the merged query have more rows than the primary table?

The secondary source may contain multiple rows with the same item code. Inspect the secondary query for duplicates before using Home > Merge Queries.

What happens if an item has no secondary match?

After the left outer merge, its expanded secondary cost is null. If the primary cost is also null, zero, or replaced from an error, the final cost will be null.

How do I handle errors in the primary cost?

In Power Query Editor, select Primary Cost and choose Transform > Replace Errors. Replace the errors with null before adding the custom column.

How can I update the results after the source costs change?

After saving the source changes, open the workbook containing the query and choose Data > Refresh All. The merge and final-cost calculation will run again.