Excel Power Query Merge: Retrieve Matching Codes from Another Table
Question details
The user needs to retrieve a code from one table and add it to a transaction table by matching the JobType column.
- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- Table2 contains transaction records and Table1 contains JobType-to-code mappings. The goal is to bring the matching code into Table2.
- Observed behavior
- The matching code is not yet included in the transaction table; the desired result is to add it through a table merge.
Confirm that both tables contain a JobType column with compatible data types, such as text in both tables. Check that the lookup table has one matching code for each JobType to avoid ambiguous results.
Use a Left Outer merge to add the matching code in Power Query
Use this method when the transaction table must retain every row while adding a code from a related lookup table.
A Left Outer merge keeps all rows from the primary transaction table and matches related rows from the lookup table. After merging, expand the new table column to select the code field.
In Excel, select the first table and choose Data > From Table/Range. Repeat for the second table, or confirm both tables already appear as queries in the Power Query Editor.
In Power Query Editor, select the transaction query, such as Table2, then choose Home > Merge Queries.
In the Merge dialog, select Table2 as the first table, select Table1 as the second table, click the JobType column in each preview, and set the join kind to Left Outer.
Click OK, then select the expand button on the new merged column. Clear the unwanted fields, select the code column, and click OK.
Review the new code column, then choose Home > Close & Load or Close & Load To to return the merged table to Excel.
Resolve unmatched JobType values before merging
Use this check when the merge returns null values or fewer matches than expected.
Use WPS Office as a Lightweight Alternative for Table Matching Work
WPS Office is a free, lightweight Microsoft Office alternative for opening, editing, and sharing Excel workbooks. It supports common XLSX table formulas and familiar spreadsheet workflows, making migration straightforward when a workbook does not depend on Excel-only Power Query refreshes.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, choose Spreadsheets, and open the XLSX workbook containing the transaction and lookup tables.
- 2. Create a matching formula: In the transaction table's code column, enter a formula such as XLOOKUP or VLOOKUP that uses JobType to search the lookup table.
- 3. Fill and review the results: Copy the formula down the transaction rows, check unmatched results, and save the workbook as XLSX for Excel compatibility.
Frequently Asked Questions
Why should I choose a Left Outer join for this Power Query merge?
A Left Outer join keeps every row from the primary transaction table, Table2, and adds matching fields from Table1. It is appropriate when no transaction should be removed because a JobType has no match.
Which table should be selected first in the Merge dialog?
Select Table2, the transaction table, as the first table if it must remain complete. Select Table1 as the matching lookup table, then choose JobType in both tables.
Why does the merged code column contain null values?
Null values usually mean that JobType is missing from the lookup table or differs between queries because of spaces, spelling, capitalization, or different data types. Compare the values and apply Transform > Format > Trim or Clean.
How do I display the code after merging two Power Query tables?
Click the expand icon on the merged column, select the code field, clear the option to use the original column name as a prefix if desired, and click OK.
Can WPS Office refresh a Microsoft Power Query merge?
Power Query is an Excel feature and may not be available in WPS Office. In WPS Spreadsheets, use compatible XLSX formulas such as XLOOKUP, VLOOKUP, or INDEX and MATCH for equivalent table-matching results.
