Merge Excel Power Query Sources and Use a Backup Cost
Question details
Combine two cost sources by item code, using the secondary cost when the primary cost is missing, zero, or unavailable.

- 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.
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.
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.
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.
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.
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.
Click the expand button on the merged column, select the secondary cost field, and click OK. Rename the expanded column Secondary Cost if necessary.
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.
Remove any columns you no longer need, check the Final Cost values, and choose Home > Close & Load to return the result to Excel.

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. 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. Add a result column: In the primary sheet, enter Final Cost in cell C1 and select cell C2.
- 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. 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.

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.




