Merge Queries —
The Power Query VLOOKUP
Join two queries together using a shared key column, choose the right join type, and selectively expand the matched data — all without a single formula.
Merge Queries is Power Query's answer to VLOOKUP — but far more reliable. Instead of a formula that breaks when rows shift, you define a permanent relationship between two queries using a shared key column (like Transaction ID). Power Query joins the tables, adds the matched data as a new column, and you then choose exactly which fields to expand into your result.
The Left Outer join is the most common type and the closest to VLOOKUP: it keeps every row from the primary (left) table and brings in matching data from the secondary (right) table where a match exists, returning null where it doesn't. Both key columns must be the same data type — a common mistake is leaving one as text and the other as a number, which produces no matches.
| # | Steps | Directions |
|---|---|---|
| 1 | Create a new query to source File 3B, and fetch the sheet named DistributionOrders. | |
| 2 | Remove top rows and promote headers. | |
| 3 | Create a new query to import Excel File 3B, OrderDestinations sheet. | |
| 4 | Promote headers. | |
| 5 | Ensure that Transaction ID in both tables is set to Whole Number type. | |
| 6 | Click on the original query from Step 1. | |
| 7 | Merge the query with additional data. | |
| 8 | Select the Order Destinations query to merge with. Then click on the Transaction ID column in each table — this will be our lookup column. | |
| 9 | Choose the join kind that represents a VLOOKUP. | |
| 10 | Click the expand arrows icon on the new merged column to expand the additional data from the other table. | |
| 11 | De-select Transaction ID — we already have it. Keep Region, Store ID, and Delivery By. | |
| 12 | Do not use original column name as prefix — uncheck that option before clicking OK. | |