Ravindra BagaleCourses & study guides

10. Power Query in Excel

10.5 Merge Queries

By Last updated:

Merge = join two tables on a key column (like XLOOKUP, but for whole tables and many columns).

Join kind Returns Typical use
Left Outer (default) All rows from first, matches from second Add store details to orders
Right Outer All rows from second, matches from first
Full Outer All rows from both Reconciliation
Inner Only matching rows Orders of known stores only
Left Anti Rows in first with no match Orders with Store ID missing in master
Right Anti Rows in second with no match Stores with no orders

Steps in Excel

  1. Select the All_Orders query › Home › Combine › Merge Queries (or as New).
  2. Top table: All_Orders, click the Store ID column. Bottom table: Stores, click Store ID.
  3. Join Kind: Left Outer › OK. The status bar shows how many rows matched.
  4. A new column Stores with Table values appears › click the expand icon (↔) › tick City Manager and Manager Email › untick Use original column name as prefix › OK.
  5. Data-quality check: merge again with Left Anti to list orders whose Store ID is missing in the master.
  6. City mapping: merge the City column with the CityMap query (5.5) on the lower-case trimmed name.

Ravindra Bagale's Tip

Merge nantar rows vadhli – asa khup students la anubhav yeto. Karan lookup table madhe key duplicate aahe (ekach Store ID don vela) – mag pratyek order don vela yete. Merge chya aadhi lookup table var Remove Duplicates (key column) kara, aani merge nantar row count compare kara.

Practice task

Merge All_Orders with Stores (Left Outer) to add City Manager, then run a Left Anti merge to find unknown Store IDs. Merge with Products to add Category.