Power Query Merge in Excel: Combine Two Tables Like VLOOKUP
Shahrukh has an Orders table with Customer IDs and a Customers table with names and cities. He wants each order with the customer's name and city next to it. He could write VLOOKUP in every row, but next month the data changes again. Power Query Merge does the join once and repeats it with one click on Refresh.
Merge Queries in Power Query joins two tables on a matching column, like a lookup for every row at once. You pick the key columns and a join kind (for example Left Outer = keep every order), then expand the columns you want.
Download the practice file (power-query-merge.xlsx)
Excel file, 8 KB. It has the sample data used below. All numbers in this workbook are fictional teaching data, not real company figures.
Quick answer
- Make both lists Excel Tables (Ctrl + T) named Orders and Customers.
- Click in Orders, then Data > From Table/Range. In Power Query, click Close & Load To > Only Create Connection. Repeat for Customers.
- Data > Get Data > Combine Queries > Merge.
- Pick Orders on top and Customers below, click Customer ID in both, choose Left Outer, click OK.
- Click the expand icon on the new column, tick Name and City, untick Use original column name as prefix, then Home > Close & Load.
The two tables
| Order ID | Customer ID | Amount |
|---|---|---|
| O-1001 | C01 | 1200 |
| O-1002 | C03 | 850 |
| O-1003 | C02 | 2300 |
| O-1004 | C01 | 640 |
| O-1005 | C05 | 990 |
| O-1006 | C04 | 1500 |
| O-1007 | C09 | 700 |
| Customer ID | Name | City |
|---|---|---|
| C01 | Ruhi | Pune |
| C02 | Zoya | Nashik |
| C03 | Amir | Nagpur |
| C04 | Rani | Kolhapur |
| C05 | Raja | Solapur |
The result after a Left Outer merge
| Order ID | Customer ID | Amount | Name | City |
|---|---|---|---|---|
| O-1001 | C01 | 1200 | Ruhi | Pune |
| O-1002 | C03 | 850 | Amir | Nagpur |
| O-1003 | C02 | 2300 | Zoya | Nashik |
| O-1004 | C01 | 640 | Ruhi | Pune |
| O-1005 | C05 | 990 | Raja | Solapur |
| O-1006 | C04 | 1500 | Rani | Kolhapur |
| O-1007 | C09 | 700 | null | null |
O-1007 has customer C09, who is not in the Customers table, so Name and City are null. Seven orders stay seven rows: a left outer join never drops an order.
Check with a formula in the practice file: =SUMIFS(Orders[Amount],Orders[Customer ID],"C01") gives 1840, the same total you get for Ruhi after the merge.
Join kinds in plain words
| Join kind | Keeps | Use it to |
|---|---|---|
| Left Outer | All rows from the first table, matches from the second | Add details to every order (most common) |
| Inner | Only rows found in both | Keep only orders with a known customer |
| Left Anti | Rows of the first table with no match | Find orders with a missing customer (O-1007) |
| Right Anti | Rows of the second table with no match | Find customers who never ordered |
| Full Outer | Everything from both | Reconcile two lists |
Refresh next month
- Paste or type new rows into the Orders and Customers tables.
- Click Data > Refresh All (Ctrl + Alt + F5).
- The merged table updates with the same steps. No formulas to copy down.
Matching tips
Keys must match exactly. Use Transform > Format > Trim and Lowercase in Power Query on both key columns if the data has stray spaces or mixed case. Make sure both keys have the same data type (Text and Text).
Ravindra Bagale's Tip
Run a Left Anti merge once before you build the report. It lists every order with a missing customer, so you fix the master data instead of explaining blank names later.
Ravindra Bagale's Tip – मराठी
Report बनवण्याआधी एकदा Left Anti merge चालवा. ज्या orders चा customer master मध्ये नाही त्यांची list मिळते, म्हणजे नंतर रिकामी नावं समजावण्यापेक्षा master data आधीच दुरुस्त होतो.
Ravindra Bagale's Tip – हिंदी
Report बनाने से पहले एक बार Left Anti merge चलाइए। जिन orders का customer master में नहीं है उनकी list मिल जाती है, तो बाद में खाली नाम समझाने के बजाय master data पहले ही ठीक हो जाता है।
Practice task
- Open the practice file. Orders and Customers are already Excel Tables.
- Load both into Power Query as connections only.
- Merge Orders with Customers on Customer ID using Left Outer and expand Name and City.
- Load the result to a new sheet and check O-1007 shows null.
- Try a Left Anti merge and check it returns only O-1007.
Learn it properly
Course lessons:
- 10.5 Merge Queries
- 10.1 Opening Power Query: Data from a Workbook or CSV
- 10.9 Refresh and Load Options
Related guides: Excel VLOOKUP Exact Match · XLOOKUP for Beginners · Power Pivot Relationships: One PivotTable from Two Tables · Excel Table vs Normal Range: When to Use Ctrl + T
Frequently asked questions
Is Power Query Merge the same as VLOOKUP?
It gives a similar result, but for the whole table at once, and it repeats on Refresh without copying formulas.
Why are my merged columns null?
The keys do not match exactly: extra spaces, different case or different data types. Clean them in Power Query first.
Why did my row count grow after the merge?
The second table has duplicate keys, so one order matched two rows. Remove duplicates from the lookup table.
Which Excel versions have Power Query?
Excel 2016 and later on Windows, and Microsoft 365. In Excel 2010 and 2013 it was a free add-in.