Table.ExpandTableColumn in Power Query M (5 Examples)

If you want to flatten a column of nested tables into regular columns in Power Query, Table.ExpandTableColumn is the function that does it. It’s the step you run right after a merge, when one column holds a whole table inside each cell. In this article I’ll show you how it works with a few practical examples.

Table of Contents

Syntax of Table.ExpandTableColumn Function

Table.ExpandTableColumn(table as table, column as text, columnNames as list, optional newColumnNames as nullable list) as table

where

table (required, table). The table that has a column of nested tables you want to expand.
column (required, text). The name of the column that holds the nested tables.
columnNames (required, list). The inner column names you want to pull out into the parent table.
newColumnNames (optional, nullable list). New names for the expanded columns, in the same order as columnNames. Leave it out to keep the inner names.

Returns: a new table where the nested tables in column are expanded into the chosen columns (and rows, when a nested table has multiple rows).

In plain terms, you point it at the column of nested tables, tell it which inner columns you want, and it spreads them out as normal columns.

Example 1: Expand a column after a merge

The most common case is right after a merge in Power Query. Table.NestedJoin matches two tables and stores the matched rows in a new nested column, then you expand it.

Here is the starting data:

Tasks:

TaskID	EmpID
T101	E1
T102	E2
T103	E1

Employees:

EmpID	EmpName
E1	Alice
E2	Bob

Merge the two tables, then expand the EmpName from the nested Emp column:

let
    Tasks = Excel.CurrentWorkbook(){[Name="Tasks"]}[Content],
    Employees = Excel.CurrentWorkbook(){[Name="Employees"]}[Content],
    Joined = Table.NestedJoin(Tasks,{"EmpID"},Employees,{"EmpID"},"Emp",JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(Joined,"Emp",{"EmpName"},{"EmpName"})
in
    Expanded

This pulls each matched employee name out of the nested table and into its own column.

The expanded result is:

TaskID	EmpID	EmpName
T101	E1	Alice
T103	E1	Alice
T102	E2	Bob

The rows come back in join order, so the two E1 tasks group together.

Example 2: Expand only a subset of columns

You don’t have to bring out every inner column. List only the ones you want in columnNames and the rest are dropped.

Here is the starting data:

Tasks2:

TaskID	EmpID
T101	E1
T102	E2

Employees2:

EmpID	EmpName	Dept	Salary
E1	Alice	Finance	60000
E2	Bob	Sales	55000

Merge, then expand only EmpName and Dept:

let
    Tasks2 = Excel.CurrentWorkbook(){[Name="Tasks2"]}[Content],
    Employees2 = Excel.CurrentWorkbook(){[Name="Employees2"]}[Content],
    Joined = Table.NestedJoin(Tasks2,{"EmpID"},Employees2,{"EmpID"},"Emp",JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(Joined,"Emp",{"EmpName","Dept"},{"EmpName","Dept"})
in
    Expanded

Salary is in the nested table but never listed, so it doesn’t appear.

The expanded result is:

TaskID	EmpID	EmpName	Dept
T101	E1	Alice	Finance
T102	E2	Bob	Sales

Only the two columns you asked for are added.

Example 3: Rename columns on expand with newColumnNames

When an inner column shares a name with an existing column, use newColumnNames to rename the columns on the way out. Here both tables have a Region column.

Here is the starting data:

Tickets:

TicketID	AgentID	Region
K1	A1	North
K2	A2	South

Agents:

AgentID	AgentName	Region
A1	Carol	West
A2	Dan	East

Expand AgentName and Region, renaming the inner Region to AgentRegion:

let
    Tickets = Excel.CurrentWorkbook(){[Name="Tickets"]}[Content],
    Agents = Excel.CurrentWorkbook(){[Name="Agents"]}[Content],
    Joined = Table.NestedJoin(Tickets,{"AgentID"},Agents,{"AgentID"},"Agent",JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(Joined,"Agent",{"AgentName","Region"},{"AgentName","AgentRegion"})
in
    Expanded

The new name keeps the two Region columns separate instead of forcing a Region.1.

The expanded result is:

TicketID	AgentID	Region	AgentName	AgentRegion
K1	A1	North	Carol	West
K2	A2	South	Dan	East

The outer Region stays put and the inner one comes in as AgentRegion.

Example 4: Group into nested tables then expand back

Merges aren’t the only source of nested tables. Table.Group can stash each group’s rows in a nested table, and you expand it the same way.

Here is the starting data:

Sales:

Region	Product	Units
North	Pens	10
North	Pads	5
South	Pens	8

Group by Region into a Lines table, then expand it back:

let
    Sales = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
    Grouped = Table.Group(Sales,{"Region"},{{"Lines",each _,type table}}),
    Expanded = Table.ExpandTableColumn(Grouped,"Lines",{"Product","Units"},{"Product","Units"})
in
    Expanded

Each nested table has multiple rows, so expanding brings every row back out.

The expanded result is:

Region	Product	Units
North	Pens	10
North	Pads	5
South	Pens	8

You end up back at the original row-per-line shape.

Example 5: Nulls from unmatched rows in a left outer join

With a left outer join, rows that don’t match keep all their left-side values but get an empty nested table. Expanding those rows produces null.

Here is the starting data:

Tasks5:

TaskID	EmpID
T101	E1
T104	E9

Employees5:

EmpID	EmpName
E1	Alice

Merge, then expand EmpName:

let
    Tasks5 = Excel.CurrentWorkbook(){[Name="Tasks5"]}[Content],
    Employees5 = Excel.CurrentWorkbook(){[Name="Employees5"]}[Content],
    Joined = Table.NestedJoin(Tasks5,{"EmpID"},Employees5,{"EmpID"},"Emp",JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(Joined,"Emp",{"EmpName"},{"EmpName"})
in
    Expanded

E9 has no matching employee, so its expanded value comes back empty.

The expanded result is:

TaskID	EmpID	EmpName
T101	E1	Alice
T104	E9	null

The unmatched row stays in the table with null for the expanded column.

Things to keep in mind with Table.ExpandTableColumn

A nested table with multiple rows multiplies rows. Expanding adds one output row per inner row, so the parent row repeats for each (Example 4). Watch your row count when nested tables aren’t single-row.
The original nested column is replaced, not kept alongside. Once you expand column, that source column is gone and only the columns you pulled out remain. There is no copy of the nested table left behind.
The column must actually hold tables. If the column holds records instead, this throws. Use Table.ExpandRecordColumn for record columns.

Performance and query folding

Against a foldable source like SQL Server, a merge plus expansion can fold back into a single join at the source. On Excel, CSV, or folder sources there’s nothing to fold, so the expansion runs locally, which is fine for typical worksheet-sized data.

Common questions about Table.ExpandTableColumn

What is the difference between Table.ExpandTableColumn and Table.ExpandRecordColumn?

Table.ExpandTableColumn expands a column of nested tables, where each cell can produce several rows. Table.ExpandRecordColumn expands a column of records, where each cell expands into columns only and never adds rows.

Why did my row count jump after expanding?

At least one nested table had more than one row. Expanding repeats the parent row once per inner row, so a group of three inner rows becomes three output rows.

List of All Power Query Functions

Related Power Query Functions / Articles: