Align Two Excel Data Sets by Customer Code with Power Query
Question details
Align two customer data sets by code while keeping codes that appear in only one table.

- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- Two Excel tables contain customer codes, names, and values for different months. The user wants a combined view without manually inserting rows.
- Observed behavior
- Matching codes should appear on the same row, and customers found in only one table should remain in the result.
Make sure each source has a customer-code column and headers. Check that codes use consistent text formatting, including leading zeros, and decide how to handle duplicate codes within each table.
Build a complete customer-code list and merge both tables
Use Power Query to align both tables against a unique list of codes and retain unmatched customers.
This method works best when each customer code appears once in each source table. Duplicate codes can produce multiple rows when the merged columns are expanded.
Select each data range and choose Insert > Table if it is not already a table. For each table, select a cell inside it and choose Data > From Table/Range to open it in Power Query.
In each query, select the customer-code column and set its data type to Text. Use Transform > Format > Trim if codes may contain extra spaces. Choose Home > Close & Load > Close & Load To and select Only Create Connection for each query.
In the Queries & Connections pane, right-click each source query and choose Reference. In each reference, select the customer-code column and choose Home > Remove Columns > Remove Other Columns. Rename both remaining columns to Customer Code, then choose Home > Append Queries > Append Queries as New and append the two references.
In the appended query, filter out blank or null codes, select Customer Code, and choose Home > Remove Rows > Remove Duplicates. Optionally sort the column to set the display order.
With the unique-code query selected, choose Home > Merge Queries. Select the first source query, match the customer-code columns, and use Left Outer as the join kind. Expand the merged column using its expand icon and select the name and monthly-value fields. Repeat the merge and expand steps for the second source query.
Rename the expanded columns as needed, then choose Home > Close & Load to place the aligned results in Excel. After source data changes, use Data > Refresh All.

Match customer tables with WPS Spreadsheet
In WPS Spreadsheet, build a combined list of customer codes, remove duplicates, and use VLOOKUP formulas to bring names and monthly values alongside each code.
- 1. Arrange the source sheets: Open your workbook in WPS Spreadsheet. For this example, name the source sheets Table1 and Table2, with customer codes in column A, names in column B, and monthly values in column C.
- 2. Combine the codes: Create a sheet named Results. Enter Customer Code in A1, then copy the codes from Table1 column A into Results starting at A2. Paste the codes from Table2 column A directly below them, excluding headers and blank cells.
- 3. Remove duplicate codes: Select the code list on Results and choose Data > Remove Duplicates. Confirm the selected column and header setting to keep one row per code.
- 4. Look up names and values: Add headers for each source's name and value in B1:E1. In B2 enter =IFERROR(VLOOKUP($A2,Table1!$A$2:$C$1000,2,FALSE),"") and in C2 use =IFERROR(VLOOKUP($A2,Table1!$A$2:$C$1000,3,FALSE),""). In D2 and E2, use the same respective formulas with Table2 in place of Table1. Fill all four formulas down, and adjust the ranges if your source data extends beyond row 1000.

Frequently Asked Questions
Will customers found in only one Excel table be kept?
Yes. The combined code list contains codes from both tables, and Left Outer merges retain every code in that list. Fields from a source without a match appear as null.
How do I update the Power Query result after adding customers?
Add the new records to the source Excel tables, then choose Data > Refresh All. The queries will rebuild the unique-code list and merge the updated data.
Why might matching customer codes fail to join?
Differences in data type, spaces, or leading zeros can prevent a match. In Power Query, set both code columns to Text and use Transform > Format > Trim where needed. Check that leading zeros are present in both sources.
What happens if a customer code appears more than once in a source?
Expanding a merged table can create multiple rows for that code. Review duplicates in the source or group them in Power Query before merging if you need one row per customer.




