Power Query Problems

How to Merge Two Excel Files with Customer IDs Using Power Query

Phi Hung Vo Sep 28, 2026 868 views

Question details

The user needs to combine two separate Excel workbooks containing Customer ID data into a single consolidated result using Power Query.

How to Merge Two Excel Files with Customer IDs Using Power Query

Product: Microsoft Excel
Device & OS: not provided
Scenario: Combining and consolidating multiple customer datasets into one unified table based on Customer IDs.
Observed behavior: The user is looking for a robust alternative to basic LOOKUP formulas, which are currently returning incomplete results, to properly merge and deduplicate records.

Before you start

Ensure both Excel files are saved in an accessible local or network folder, and format the data in both files as Excel Tables (Ctrl+T) with identically named 'Customer ID' column headers.

Solution 1Recommended

Use Append Queries to Combine Customer ID Tables

Appending queries stacks the data from both files on top of each other. This is ideal if both files share the same column structure and you want a single, master list of all Customer IDs.

If your two workbooks contain identical column headers (such as Customer ID, Name, and Email), appending them will create a consolidated master list.

After appending, you can use Power Query's built-in tools to remove any duplicate Customer IDs that may exist across both files.

Import the First File

Open a blank Excel workbook. Go to the 'Data' tab, click 'Get Data', select 'From File', and then 'From Workbook'. Choose your first file and click 'Transform Data'.

Import the Second File

In the Power Query Editor, go to the 'Home' tab, click 'New Source', choose 'File' > 'Excel', and select your second file.

Append the Queries

Select your first query on the left pane. On the 'Home' tab, click 'Append Queries'. Choose 'Two tables', select your second query in the dropdown, and click 'OK'.

Remove Duplicate Customer IDs

Select the 'Customer ID' column in the newly appended table. Right-click the column header and choose 'Remove Duplicates' to ensure each customer only appears once.

Load the Merged Data

Click 'Close & Load' on the Home tab to output the consolidated data into a new worksheet in your current workbook.

Use Append Queries to Combine Customer ID Tables

Data Connection: Power Query maintains a connection to the original files. If you add new Customer IDs to the source files, you can simply click 'Refresh' in your master file to update the data.

Solution 2

Use Merge Queries to Join Data by Customer ID

Merging queries is similar to a VLOOKUP. It allows you to align rows from both files based on a matching Customer ID, which is useful when the files contain different pieces of information for the same customers.

Effortless Data Merging

Consolidate Multiple Excel Files Easily with WPS Spreadsheet

If you find Power Query too complex or intimidating, WPS Office offers an intuitive 'Merge Workbooks' tool. It allows you to combine multiple sheets or files containing Customer IDs in just a few clicks without setting up data connections or writing formulas.

1. Open WPS Spreadsheet: Launch WPS Office and create a new blank Spreadsheet document.
2. Access the Merge Tool: Navigate to the 'Data' tab on the top ribbon and click on the 'Merge' or 'Consolidate' button, then select 'Merge Multiple Workbooks'.
3. Add Your Files: In the pop-up window, click 'Add Files' and select the workbooks containing your Customer ID datasets.
4. Execute the Merge: Follow the prompt to choose how you want to combine the sheets, then click 'Merge'. WPS will automatically generate a new workbook containing your consolidated data.

Built-in 'Merge Workbooks' utility for instant data consolidation100% compatible with Microsoft Excel formats (.xlsx, .xls, .csv)Intuitive and familiar ribbon interface with zero learning curveFree and lightweight alternative to heavy data processing software

microsoft office alternative - wps office

Frequently Asked Questions

What is the difference between Append and Merge in Power Query?

Append adds rows from one table to the bottom of another table (ideal for combining identical lists of Customer IDs). Merge adds columns from one table to another based on a matching column, similar to a VLOOKUP (ideal for combining different details about the same Customer ID).

Why does my LOOKUP function fail when matching Customer IDs?

LOOKUP or VLOOKUP functions often fail if the Customer IDs are formatted differently across the two files (e.g., one is formatted as Text and the other as a Number), or if there are trailing spaces. Power Query solves this by allowing you to standardize the data types before merging.

Will my merged Power Query table update if the original files are changed?

Yes. Power Query creates a dynamic connection to the source files. If you add, delete, or modify Customer IDs in the original Excel workbooks, you just need to right-click your merged table and select 'Refresh' to update the data.

Can I merge more than two Excel files at once?

Yes. In Power Query, you can select 'Three or more tables' when using the Append Queries feature, or you can use 'Get Data from Folder' to automatically merge all Excel files located within a specific directory.

Excel & Data Problems