The six join kinds in Excel Power Query are Left Outer, Right Outer, Full Outer, Inner, Left Anti, and Right Anti. You choose one when you merge two tables, and it decides which rows from the first table (left) and second table (right) appear in the result. The default is Left Outer, which keeps all rows from the left table and matches rows from the right table where the key columns are equal.
What a Merge Actually Does
Power Query’s Merge Queries command (available on the Home tab in the Power Query Editor) combines two tables side by side based on matching values in one or more columns. It is not the same as Append Queries, which stacks tables vertically. A merge is analogous to a SQL JOIN or an Excel VLOOKUP, but with more flexibility and no formula required.
When you merge, you select two queries (or tables) and click the key columns in each. Power Query then asks you to pick a join kind. The output is a new column in the left table that contains a nested table for each row, showing the matching rows from the right table. You usually expand that column to bring the right table’s fields into the main table.
The Six Join Kinds in Detail
Here is what each join kind returns, using two simple tables: Left (ID, Name) and Right (ID, Score). Left has IDs 1, 2, 3; Right has IDs 2, 3, 4.
- Left Outer (default): All rows from Left (1, 2, 3) plus matching Score from Right. ID 1 gets a null Score because there is no match.
- Right Outer: All rows from Right (2, 3, 4) plus matching Name from Left. ID 4 gets a null Name.
- Full Outer: All rows from both tables. IDs 1 and 4 appear with nulls in the missing side.
- Inner: Only rows with matches in both tables (IDs 2 and 3).
- Left Anti: Only rows from Left that have no match in Right (ID 1). Useful for finding missing records.
- Right Anti: Only rows from Right that have no match in Left (ID 4).
In the Power Query UI, the join kinds are named exactly as above. The anti joins are sometimes called “Left Anti” and “Right Anti” in documentation, but the dialog lists them as “Left Anti” and “Right Anti” as well.
How to Choose the Right Join Kind
Your choice depends on what you want to keep. Start by asking: “Which table is my primary table?” That is your left table. Then decide whether you need unmatched rows from that primary table, from the other table, or from both.
- If you want to keep every row in your main table and add related info, use Left Outer.
- If you want to keep every row in the lookup table and add info from the main table, use Right Outer.
- If you want a complete list of all keys, regardless of which table they appear in, use Full Outer.
- If you only want rows that exist in both tables, use Inner.
- If you want to find records in your main table that are missing from the other, use Left Anti.
- If you want to find records in the other table that are missing from your main, use Right Anti.
A common pattern is to use Left Outer for a typical lookup, then later filter out nulls if you only want matches. But using Inner directly is more efficient because it avoids creating extra rows.
Step-by-Step: Performing a Merge
Here is the exact process in Power Query (Microsoft 365, Windows):
- Load both tables into Power Query Editor (Data > Get Data > From Table/Range, or via other sources).
- Select one query in the left pane. That becomes your left table.
- Go to Home > Merge Queries (or Merge Queries as New if you want a separate query).
- In the dialog, select the second query in the top dropdown.
- Click the key column in the left table preview, then click the matching key column in the right table preview. Hold Ctrl to select multiple columns.
- Choose a Join Kind from the dropdown. The default is Left Outer.
- Click OK. A new column named after the right table appears, containing tables.
- Click the expand icon (double arrow) in the column header, select the columns you want, and uncheck “Use original column name as prefix” if you prefer clean names.
On Mac, the ribbon is similar: Home > Merge Queries works the same way. In Excel for the web, Power Query is not available; you would need to use Power Query in the desktop app or use formulas like XLOOKUP instead.
Common Problems and How to Fix Them
Several issues trip up users new to merging:
- Data type mismatches: If the key columns are different types (e.g., text vs. number), the merge fails or returns no matches. Fix by changing the column type in the Transform tab before merging.
- Duplicate keys: If the right table has duplicate keys, the merge produces multiple rows for each match. This is expected, but can inflate your data. Use Remove Duplicates on the right table first if you only want one match.
- Nulls in key columns: Nulls never match each other in a merge. If you need nulls to match, replace nulls with a placeholder value (e.g., 0 or “Unknown”) before merging.
- Expanding nested tables: If you forget to expand the new column, you only see “Table” in each cell. Click the expand icon to bring in the fields.
- Performance issues: Merging two large tables can be slow. Consider filtering rows before merging, or using Query Folding when the data source is a database.
Merge vs. Lookup Functions: When to Use Which
Power Query merges are not the only way to combine data in Excel. The table below compares common approaches:
- Power Query Merge: Best for one-time or repeatable transformations, handles large data, and can be refreshed. Requires data in tables or external sources.
- VLOOKUP/XLOOKUP: Good for quick, formula-based lookups in a single worksheet. XLOOKUP is available in Excel 2021 and Microsoft 365. It returns one value per lookup, and does not combine multiple columns easily without extra formulas.
- INDEX/MATCH: Older but flexible; works in all Excel versions. Still limited to one result per lookup.
- Power Pivot Relationships: For data modeling and pivot tables, relationships are more efficient than merges because they don’t duplicate data. Use when you need to aggregate across tables.
If you need to join two tables and then further transform the data (like filtering, grouping, or calculating), Power Query is the right tool. If you just need a quick lookup in a cell, use XLOOKUP.
Frequently Asked Questions
What is the difference between Left Outer and Inner join?
Left Outer keeps all rows from the left table, even if there is no match in the right table (those rows get nulls for right-table columns). Inner keeps only rows that have a match in both tables. So Left Outer includes unmatched left rows; Inner excludes them.
Can I merge on multiple columns?
Yes. In the Merge dialog, hold Ctrl and click multiple columns in each table. Power Query will match on the combination of those columns (an AND condition). This is useful when a single column is not unique.
Why does my merge return more rows than the left table?
That happens when the right table has duplicate keys. Each duplicate in the right table creates an additional row in the result. For example, if left has one row with ID=1 and right has two rows with ID=1, the merge produces two rows. To avoid this, remove duplicates from the right table before merging, or use a join kind that limits matches (though no join kind automatically deduplicates).
Does Power Query merge work like a VLOOKUP?
Conceptually yes, but with differences. A VLOOKUP returns the first match only, while a merge returns all matches (which can be multiple rows). Also, a merge can bring in many columns at once and is refreshable. VLOOKUP is a formula that recalculates when data changes; a merge requires you to refresh the query.
What is a fuzzy merge?
Power Query has a “Fuzzy merge” option (in the Merge dialog, under “Join Kind” there is a checkbox for fuzzy matching). It matches approximate text values, like “John Smith” vs. “Jon Smith”. It is not one of the six join kinds; it is an optional matching algorithm that can be applied to any join kind. Use it carefully because it can produce false matches.
Final Tips for Clean Merges
Always check the data types of your key columns before merging. Use the Transform > Data Type dropdown to set them to Text or Whole Number, whichever is appropriate. Also, name your columns clearly before merging so you know which table each field comes from. Finally, if you are merging two queries that come from the same source, consider using Reference to avoid duplicating the entire data load.
Related
- Excel Insert Yes No Checkbox: 2 Ways
- Excel Instrument Repair Vernon CT: Full Guide
- Excel Interpolate Between Numbers: How To
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Schedule an Email in Outlook Monthly with Power AutomateOutlook does not have a built-in option to send the same email on a monthly repeat. To automate…
- How to Create Name Labels in Word: A Step-by-Step GuideTo create a sheet of name labels in Microsoft Word, go to Mailings > Start Mail Merge >…
- Microsoft Excel CSV Import: Steps, Settings, and FixesTo import a CSV file into Microsoft Excel for Microsoft 365 on Windows, select Data > From Text/CSV,…
- How to Freeze Selected Rows in Excel While You ScrollTo freeze selected rows in Excel, click the cell in column A immediately below the last row you…
- Cannot Remove a Section Break in Word? Try These FixesYes, you can remove a section break in Word: turn on formatting marks, locate the break, click immediately…
- Google Sheets Daily Schedule Template: Build Your OwnA Google Sheets daily schedule template is a reusable spreadsheet for organizing the day by time, activity, priority,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.