Table.ExpandTableColumn
Table function · By Tahir Nas · Examples run in our simulator · Published · Updated
Expands a nested table column into columns (and rows).
When to use it
Use it after a merge or an All Rows group to turn the nested tables back into columns. Pick only the columns you need and give them clear names. If you only need a total, aggregate instead of expanding.
Syntax
Table.ExpandTableColumn(table as table, column as text, columnNames as list, optional newColumnNames as nullable list) as tableParameters
- columnNames
- Columns to take from the nested tables.
- newColumnNames
- Optional output names.
Returns
The table with nested columns expanded.
Example
Table.ExpandTableColumn(#table({"K", "T"}, {{"a", #table({"x"}, {{1}, {2}})}}), "T", {"x"})Result
| K | x |
|---|---|
| a | 1 |
| a | 2 |
Example: expand with new names
The fourth argument renames the expanded columns, avoiding clashes with existing ones.
Table.ExpandTableColumn(#table({"Order", "C"}, {{1, #table({"Name"}, {{"Ana"}})}}), "C", {"Name"}, {"Customer"})Result
| Order | Customer |
|---|---|
| 1 | Ana |
Both results are produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M, and checked on every build.
Good to know
- A nested table with several rows produces several output rows.
Common mistakes
- Expanding every column by default and loading unneeded data.
- Expanding a name that clashes with an existing column.
The matching command in Excel
- Excel for Windows
- The expand icon on a table column's header
- What you see
- You tick the fields of the nested table that should become columns.
Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Merge queries overview (Microsoft Learn).
Exercises that use Table.ExpandTableColumn
- intermediate~9 minPro
Look up each order's customer city (kite shop)
Merge Orders with Customers on Customer and expand City, keeping every order.
- intermediate~12 minPro
Combine every monthly CSV in a folder (kite shop)
Read every file in the folder, keep the file name, and stack the orders into one typed table.
- advanced~10 minPro
Look up prices by shop and product together (farm shops)
Merge Sales with Prices on both Store and Product, then calculate line revenue.
- advanced~12 minPro
Rank each employee's claims by amount (consultancy)
Within each employee, number claims from largest to smallest Amount.
- intermediate~8 minPro
Flag every order from a repeat customer (kite shop)
Keep every order, and add Repeat Customer = true on all orders of customers who ordered more than once.
- advanced~11 minPro
Append every monthly table in a workbook (kite shop)
A workbook has one Excel table per month (Sales_Jan, Sales_Feb…) plus a notes sheet and a targets table. Append just the monthly sales tables.
Learn more
- Merge queries: choosing a join kind lesson
- Merge vs append in Power Query: which one do you need? guide
Related functions
Official documentation: Table.ExpandTableColumn on Microsoft Learn.