Most Power Query users only ever click Merge and expand the column — but that leaves a lot of power on the table. This lesson teaches you exactly how Table.NestedJoin, Table.Join, and the GUI Merge operation work under the hood, when to use each, and how to avoid the join bugs that silently corrupt your data.
You have two tables — one with sales transactions and one with customer details — and you need to bring them together so each sale shows the customer's name, region, and tier. In Excel, you'd reach for VLOOKUP. In SQL, you'd write a JOIN. In Power Query, you have three distinct tools for this job: the GUI Merge operation, the Table.NestedJoin function, and the lower-level Table.Join function. They all combine tables, but they work differently under the hood, produce different intermediate structures, and suit different scenarios.
Most Power Query users only ever click the Merge button in the GUI and expand the resulting column. That works fine until it doesn't — until you need to join on multiple keys with custom logic, or keep the nested table around for aggregation, or understand why your row count exploded after a merge. At that point, the abstraction breaks down and you need to understand what's actually happening in the M code.
By the end of this lesson, you'll be able to read and write all three forms of table joining in Power Query M, choose the right approach for a given problem, and troubleshoot the most common mistakes that trip people up.
What you'll learn:
Table.NestedJoin actually producesTable.Join differs from Table.NestedJoin and when to prefer itYou should be comfortable with the basics of Power Query — loading data, stepping through the Applied Steps pane, and reading simple M expressions. You don't need to be an M expert, but you should know what a query step looks like and how to open the Advanced Editor. If you haven't written any M by hand yet, that's okay; we'll walk through every line.
Imagine you work at a regional distributor. You have two tables loaded into Power Query:
Orders table:
| OrderID | CustomerID | Product | Amount |
|---|---|---|---|
| 1001 | C100 | Widget | 250 |
| 1002 | C101 | Gadget | 175 |
| 1003 | C100 | Widget | 310 |
| 1004 | C999 | Sprocket | 90 |
Customers table:
| CustomerID | CustomerName | Region | Tier |
|---|---|---|---|
| C100 | Acme Corp | West | Gold |
| C101 | Beta Ltd | East | Silver |
| C102 | Gamma Inc | North | Bronze |
Notice that OrderID 1004 references customer C999, who doesn't exist in the Customers table. And C102 exists in Customers but has no orders. These edge cases will matter when we talk about join kinds.
When you use the Power Query ribbon to merge queries (Home tab → Merge Queries), the GUI writes a specific M function call for you. Let's demystify it.
After clicking Merge Queries on the Orders query, selecting Customers as the second table, matching on CustomerID, and accepting the default Left Outer join, Power Query writes this step into your M code:
Source = Table.NestedJoin(
Orders,
{"CustomerID"},
Customers,
{"CustomerID"},
"Customers",
JoinKind.LeftOuter
)
Open the Advanced Editor (View tab → Advanced Editor) and you'll see this exact pattern. The result isn't a flat joined table — it's your original Orders table with one new column called "Customers" that contains an entire nested table in each cell. That's the signature behavior of Table.NestedJoin.
Think of it like this: instead of immediately flattening everything together, Power Query places the matching rows from the Customers table into a little package (a table) and slides it into a new column. Your Orders table still has four rows, but now each row carries a tiny related table tucked inside the "Customers" column.
You can verify this by looking at the column — each cell displays the text "[Table]". Click one of those cells and the preview pane at the bottom shows you the rows from Customers that match that particular order.
The full signature of Table.NestedJoin is:
Table.NestedJoin(
table1 as table,
key1 as any, // column name or list of column names
table2 as table,
key2 as any, // column name or list of column names
newColumnName as text, // name for the nested table column
joinKind as number // JoinKind.LeftOuter, JoinKind.Inner, etc.
)
The key1 and key2 parameters can be a single column name in quotes or a list of column names in curly braces. When joining on multiple columns, you pass a list to each.
The nested table design is intentional and actually powerful. Because the match results are packaged as a table rather than immediately expanded, you can choose exactly what to do with them next:
The step you almost always add after Table.NestedJoin is Table.ExpandTableColumn:
Expanded = Table.ExpandTableColumn(
Source,
"Customers", // column containing the nested tables
{"CustomerName", "Region", "Tier"}, // columns to bring in from nested table
{"CustomerName", "Region", "Tier"} // names to give them in the result
)
The third argument is the list of column names from the nested table you want to keep. The fourth argument lets you rename them on arrival — useful when both tables share a column name other than the join key (e.g., both have an "UpdatedDate" column).
After this expansion, you have a flat table with six columns: OrderID, CustomerID, Product, Amount, CustomerName, Region, Tier. That's your familiar join result.
Tip: In the GUI, Power Query generates the
Table.ExpandTableColumnstep automatically when you click the expand icon (the icon with two outward arrows at the top of the nested column). You can then look in the Advanced Editor to see exactly what code it produced — a great way to learn M syntax quickly.
Here's where Table.NestedJoin beats a flat join: suppose you want to add a column to your Customers table showing how many orders each customer has placed, and the total revenue. You don't need to expand the nested Orders table at all.
let
// Start from Customers as the left table
WithOrders = Table.NestedJoin(
Customers,
{"CustomerID"},
Orders,
{"CustomerID"},
"Orders",
JoinKind.LeftOuter
),
// Add an OrderCount column by counting rows in each nested table
WithCount = Table.AddColumn(
WithOrders,
"OrderCount",
each Table.RowCount([Orders])
),
// Add a TotalRevenue column by summing the Amount column of each nested table
WithRevenue = Table.AddColumn(
WithCount,
"TotalRevenue",
each List.Sum([Orders][Amount])
),
// Remove the now-unnecessary nested column
Result = Table.RemoveColumns(WithRevenue, {"Orders"})
in
Result
This produces a clean Customers table with computed aggregates — no intermediate expanded rows, no group-by step needed after the fact. The each keyword means "for each row," and [Orders] refers to the nested table in that row's Orders column. [Orders][Amount] drills into that nested table and returns the list of values in its Amount column.
Table.Join is the lower-level counterpart to Table.NestedJoin. It does not produce a nested column — it immediately produces a flat, fully merged table, much like a SQL JOIN.
Table.Join(
table1 as table,
key1 as any,
table2 as table,
key2 as any,
joinKind as number, // optional, defaults to JoinKind.Inner
joinAlgorithm as number, // optional
keyEqualityComparers as list // optional
)
Using our scenario:
FlatJoin = Table.Join(
Orders,
"CustomerID",
Customers,
"CustomerID",
JoinKind.LeftOuter
)
The result immediately has all columns from both tables side by side. No nested tables, no expansion step. If both tables have a column with the same name (other than the join key), Power Query handles it by keeping both — typically suffixing with a number. You'll often need a rename step afterward to clarify.
| Situation | Recommended Approach |
|---|---|
| Simple lookup — bring a few columns from a second table | Either works; Table.NestedJoin + expand is what the GUI writes |
| Aggregate the related table (count, sum) without expanding | Table.NestedJoin — keep the nesting |
| Need an immediately flat result, all columns | Table.Join — skip the expand step |
| Column name conflicts across tables | Table.NestedJoin + selective expand with renaming gives you more control |
| Dynamic or computed join keys | Table.Join with keyEqualityComparers for custom matching logic |
Warning:
Table.Joincan be less predictable when both tables share column names beyond the join key. Always inspect the result immediately and rename ambiguous columns before using them downstream.
Both functions accept the same join kind constants. Understanding what each does is critical — using the wrong one is the most common source of mysterious row count changes.
Only rows where the key exists in both tables survive. In our scenario:
Result: 3 rows. Customer C102 (Gamma Inc) also disappears because they have no orders.
InnerJoin = Table.NestedJoin(Orders, {"CustomerID"}, Customers, {"CustomerID"}, "Customers", JoinKind.Inner)
All rows from the left table (Orders) survive. Rows from the right (Customers) fill in where matches exist; cells are null where they don't.
Result: 4 rows. This is the default in the GUI for good reason — you rarely want to lose transaction records just because a customer lookup fails.
All rows from the right table (Customers) survive. The left table (Orders) fills in where matches exist.
Result: 3 rows — one for Acme Corp (twice, for two orders... wait, this is where it gets interesting).
Actually, with a right outer join here, you'd get: Acme Corp twice (matched to orders 1001 and 1003), Beta Ltd once, and Gamma Inc once with nulls — 4 rows total. The customer table drives the base set, but matching orders still fan out.
All rows from both tables survive. Nulls appear wherever a match doesn't exist on either side. Result: 5 rows — the 4 orders plus Gamma Inc as an unmatched customer.
Only rows from the left table that have no match in the right table. This is your "find orphans" join.
In our scenario: only Order 1004 (C999). Useful for data quality checks — "show me all orders with a customer ID that doesn't exist in our customer master."
The mirror: only rows from the right table with no match on the left. In our scenario: only Gamma Inc (C102). Useful for finding "customers who have never ordered."
Tip: LeftAnti and RightAnti are underused but incredibly valuable for data quality reporting. Build a separate query specifically to catch unmatched records before they silently become nulls in your final model.
Sometimes a single column isn't a unique key. Imagine your Orders table uses both a Region and an OrderType to look up a rate from a pricing table. You join on both columns simultaneously.
With Table.NestedJoin, pass a list to both key parameters:
WithRates = Table.NestedJoin(
Orders,
{"Region", "OrderType"},
PricingTable,
{"Region", "OrderType"},
"Pricing",
JoinKind.LeftOuter
)
The order of columns in both lists must correspond — the first item in the left list matches against the first item in the right list. The same syntax works identically for Table.Join.
One of the most frustrating join bugs happens when the key column in Table 1 is typed as text and the key column in Table 2 is typed as number (or any). Power Query will not match them, even if the values look the same. You'll get zero matches and a table full of nulls.
Always make sure join key columns are the same type before joining. Add a type conversion step:
OrdersFixed = Table.TransformColumnTypes(
Orders,
{{"CustomerID", type text}}
),
CustomersFixed = Table.TransformColumnTypes(
Customers,
{{"CustomerID", type text}}
)
Then join on the typed versions. This is especially common when one source is a CSV (which often loads IDs as text) and the other is a database (which returns IDs as integers).
Warning: The
anytype is a silent trap. A column typed asanylooks like it should match anything, but join key matching respects underlying value types. Convert explicitly rather than assuminganywill handle it.
Work through this exercise in Power Query. You can use Excel (Data tab → Get Data → Launch Power Query Editor) or Power BI Desktop (Transform Data).
Setup: Create two blank queries using Enter Data (Home tab → Enter Data) with the Orders and Customers tables from the scenario at the top of this lesson.
Step 1 — Nested Join with Expansion
In the Orders query, open the Advanced Editor and write a complete M query:
let
Orders = #table(
{"OrderID", "CustomerID", "Product", "Amount"},
{{1001, "C100", "Widget", 250}, {1002, "C101", "Gadget", 175},
{1003, "C100", "Widget", 310}, {1004, "C999", "Sprocket", 90}}
),
Customers = #table(
{"CustomerID", "CustomerName", "Region", "Tier"},
{{"C100", "Acme Corp", "West", "Gold"}, {"C101", "Beta Ltd", "East", "Silver"},
{"C102", "Gamma Inc", "North", "Bronze"}}
),
Joined = Table.NestedJoin(Orders, {"CustomerID"}, Customers, {"CustomerID"}, "Customers", JoinKind.LeftOuter),
Expanded = Table.ExpandTableColumn(Joined, "Customers", {"CustomerName", "Region", "Tier"}, {"CustomerName", "Region", "Tier"})
in
Expanded
Observe that Order 1004 has nulls for CustomerName, Region, and Tier.
Step 2 — Change to Inner Join
Change JoinKind.LeftOuter to JoinKind.Inner. How many rows do you get? Confirm that Order 1004 disappears.
Step 3 — Find Unmatched Orders
Change the join kind to JoinKind.LeftAnti and remove the expand step (since LeftAnti returns only left-table columns). You should get exactly one row: Order 1004.
Step 4 — Aggregate Without Expanding
Swap the left and right tables (put Customers first) and use JoinKind.LeftOuter, naming the nested column "Orders". Then add columns for OrderCount and TotalRevenue using Table.RowCount and List.Sum as shown in the earlier example. Verify that Gamma Inc shows 0 for OrderCount and null (or 0 if you wrap in a null coalesce) for TotalRevenue.
"My row count doubled (or tripled) after the merge." This is a many-to-many join. If your join key is not unique in the right table, each matching left row will produce multiple output rows — one for each match. For example, if Customers had two rows with CustomerID C100, every order for C100 would appear twice. Check for duplicates in your lookup table's key column before joining. You can use Table.Distinct or add a deduplication step.
"All my expanded columns are null even though the data looks right." Almost certainly a type mismatch on the join key. Check the type icons at the top of each key column in the Query Editor. If one is ABC (text) and the other is 123 (whole number), explicitly convert both to the same type before joining.
"I can't find Table.Join or Table.NestedJoin in the GUI." That's because these functions are what the GUI writes for you — you read and edit them in the Advanced Editor. The GUI surface for joining is Merge Queries on the Home tab. Once you merge, open the Advanced Editor to see the generated code and modify it.
"I tried to expand the nested column but some columns are missing from the expand list." The expand dialog samples the nested tables to determine available columns. If some nested tables are empty (no match for that row), Power Query may miss columns that only appear in matched rows. Fix: add a step before expanding to ensure all nested tables have consistent schema, or type the nested column explicitly.
"I used Table.Join and now I have two columns called 'UpdatedDate' with no clear names."
Table.Join doesn't give you control over column naming during the join — you need a follow-up Table.RenameColumns step. With Table.NestedJoin + expand, you use the fourth argument to Table.ExpandTableColumn to rename on arrival, which is cleaner.
You now understand the three-layer architecture of table combining in Power Query M. The GUI Merge operation is a convenient wrapper around Table.NestedJoin, which packages matches as nested tables for maximum flexibility. Expanding those nested tables gives you flat join results. Table.Join skips the nesting and flattens immediately, trading flexibility for brevity. Both functions accept the same six join kinds, and choosing the right join kind is what controls which rows survive in your output.
The key mental model to take away: Table.NestedJoin defers the decision about what to do with the matched data; Table.Join makes that decision immediately. When you want to aggregate related rows, defer. When you want a flat result with all columns, go direct.
Where to go next:
Table.Join's optional keyEqualityComparers parameter lets you define what "equal" means, enabling case-insensitive joins or date-range matchingPractice by taking a real dataset you work with and deliberately trying all six join kinds against a lookup table. Watch what happens to your row count each time. That hands-on intuition will make you dramatically faster at diagnosing join problems in production queries.