Power Query Problems

Align Two Excel Data Sets by Customer Code with Power Query

Jason MillerJason Miller Nov 14, 2026 868 views

Question details

Align two customer data sets by code while keeping codes that appear in only one table.

Align Two Excel Data Sets by Customer Code with Power Query
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.
Before you start

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.

Solution 1Recommended

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.

1
Create source queries

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.

2
Standardize customer codes

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.

3
Create a combined code list

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.

4
Remove duplicate codes

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.

5
Merge and expand both tables

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.

6
Load the result

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.

Build a complete customer-code list and merge both tables
Unmatched customers: A customer found in only one source remains in the result; the expanded fields from the other source show null.
Align customer data in WPS Office

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. 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. 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. 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. 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.
Align matching customer codes while retaining unmatched codesOpen and edit Microsoft Excel XLSX filesUse familiar spreadsheet formulas and data toolsWork with a free, easy-to-use office suite
microsoft office alternative - wps office

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.