Table.ExpandRecordColumn in Power Query M (5 Examples)

If you have a column that holds a record in each cell and you want to pull those record fields out into their own columns, the Table.ExpandRecordColumn function is what you reach for. In this article, I’ll show you how to use it to expand all fields, pick a subset, and rename the new columns as they come out.

Table of Contents

Syntax of Table.ExpandRecordColumn Function

Table.ExpandRecordColumn(table as table, column as text, fieldNames as list, optional newColumnNames as nullable list) as table

where

table (required, table). The table that contains the record column you want to expand.
column (required, text). The name of the column whose records you want to expand.
fieldNames (required, list). The record field names to pull out, each becoming its own column.
newColumnNames (optional, nullable list). New names for the expanded columns, in the same order as fieldNames. Omit it to keep the original field names.

Returns: a table with the chosen record fields added as new columns and the original record column removed.

In plain terms, you point it at a column of records, list the fields you want, and it spreads those fields into flat columns.

Example 1: Expand all fields of a record column

Say you have an Employees table and each row carries a Details record with the person’s city and title.

Here is the flat starting data:

EmpID	FirstName	City	Title
E1	Aanya	Pune	Analyst
E2	Marco	Lisbon	Manager
E3	Naomi	Osaka	Engineer

A Details record is built from City and Title, then expanded back into columns.

let
    Source = Excel.CurrentWorkbook(){[Name="Employees"]}[Content],
    AddDetails = Table.AddColumn(Source,"Details",each [City=[City],Title=[Title]]),
    Trimmed = Table.SelectColumns(AddDetails,{"EmpID","FirstName","Details"}),
    Expanded = Table.ExpandRecordColumn(Trimmed,"Details",{"City","Title"})
in
    Expanded

Both record fields come back out as their own columns:

EmpID	FirstName	City	Title
E1	Aanya	Pune	Analyst
E2	Marco	Lisbon	Manager
E3	Naomi	Osaka	Engineer

The Details column is gone, replaced by City and Title.

Example 2: Expand only a subset of record fields

You don’t have to pull out every field. Just list the ones you want in fieldNames.

Here is the starting data, with a Specs record holding Price, Weight, and Category:

SKU	ProductName	Price	Weight	Category
P100	Desk Lamp	29.95	1.2	Lighting
P200	Office Chair	149	11.5	Seating

Only Price and Category are expanded, so Weight stays inside the record and is dropped.

let
    Source = Excel.CurrentWorkbook(){[Name="Products"]}[Content],
    AddSpecs = Table.AddColumn(Source,"Specs",each [Price=[Price],Weight=[Weight],Category=[Category]]),
    Trimmed = Table.SelectColumns(AddSpecs,{"SKU","ProductName","Specs"}),
    Expanded = Table.ExpandRecordColumn(Trimmed,"Specs",{"Price","Category"})
in
    Expanded

The result keeps only the fields you asked for:

SKU	ProductName	Price	Category
P100	Desk Lamp	29.95	Lighting
P200	Office Chair	149	Seating

Weight never makes it into the output because it wasn’t in fieldNames.

Example 3: Rename columns on expand with newColumnNames

Pass a fourth argument to name the new columns as they come out, instead of keeping the field names.

Here is the starting data, with a Contact record holding Manager and Phone:

StoreID	Region	Manager	Phone
S1	North	Priya	555-0100
S2	South	Liam	555-0200

The two fields are expanded and renamed to ContactManager and ContactPhone.

let
    Source = Excel.CurrentWorkbook(){[Name="Stores"]}[Content],
    AddContact = Table.AddColumn(Source,"Contact",each [Manager=[Manager],Phone=[Phone]]),
    Trimmed = Table.SelectColumns(AddContact,{"StoreID","Region","Contact"}),
    Expanded = Table.ExpandRecordColumn(Trimmed,"Contact",{"Manager","Phone"},{"ContactManager","ContactPhone"})
in
    Expanded

The new columns carry the names you supplied:

StoreID	Region	ContactManager	ContactPhone
S1	North	Priya	555-0100
S2	South	Liam	555-0200

newColumnNames lines up with fieldNames by position, so the first field gets the first new name.

Example 4: Rename to avoid a name clash with an existing column

If a field name already exists as a column in the table, you have to rename it on expand. Otherwise the expand fails on the duplicate.

Here is the starting data. The table already has a Region column, and the Location record also carries a Region field:

PersonID	Region	HomeCity	WorkCity
U1	West	Denver	Boulder
U2	East	Newark	Trenton

The record fields are renamed to WorkRegion and WorkCity so they don’t collide with the existing Region column.

let
    Source = Excel.CurrentWorkbook(){[Name="People"]}[Content],
    AddLocation = Table.AddColumn(Source,"Location",each [Region=[Region],City=[WorkCity]]),
    Trimmed = Table.SelectColumns(AddLocation,{"PersonID","Region","Location"}),
    Expanded = Table.ExpandRecordColumn(Trimmed,"Location",{"Region","City"},{"WorkRegion","WorkCity"})
in
    Expanded

The original Region column stays untouched, and the expanded fields sit beside it:

PersonID	Region	WorkRegion	WorkCity
U1	West	West	Boulder
U2	East	East	Trenton

Here WorkRegion happens to repeat the Region value because the record copied it, but they’re two separate columns.

Example 5: Expand a parsed record into named report columns

A common real-world case is a record that came from parsing JSON or an API response, where the field names aren’t what you want in the final report. If you’re newer to all this, the getting started with Power Query guide covers the basics.

Here is the starting data, with an Info record holding Country, Amount, and Status:

OrderID	CustomerName	Country	Amount	Status
O5001	Hannah	Canada	250.5	Shipped
O5002	Diego	Mexico	99	Pending
O5003	Sven	Sweden	412.75	Shipped

All three fields are expanded and renamed to report-friendly names in one step.

let
    Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
    AddInfo = Table.AddColumn(Source,"Info",each [Country=[Country],Amount=[Amount],Status=[Status]]),
    Trimmed = Table.SelectColumns(AddInfo,{"OrderID","CustomerName","Info"}),
    Expanded = Table.ExpandRecordColumn(Trimmed,"Info",{"Country","Amount","Status"},{"ShipCountry","OrderAmount","OrderStatus"})
in
    Expanded

The output is clean, flat, and ready to load:

OrderID	CustomerName	ShipCountry	OrderAmount	OrderStatus
O5001	Hannah	Canada	250.5	Shipped
O5002	Diego	Mexico	99	Pending
O5003	Sven	Sweden	412.75	Shipped

Expanding and renaming together saves a separate Table.RenameColumns step.

Things to keep in mind with Table.ExpandRecordColumn

Field names are case-sensitive. "city" will not match a City field. Match the case exactly or the field comes back missing.
A field that isn’t in the record gives a null column. Naming a field that doesn’t exist produces a column full of null rather than an error, so a column of blanks usually means a misspelled or missing field.
newColumnNames must match fieldNames by length and order. Supply one new name per field, in the same sequence. A shorter or longer list throws an error.

Common questions about Table.ExpandRecordColumn

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

Table.ExpandRecordColumn expands a column of records, where each cell holds one set of fields and the row count stays the same. Table.ExpandTableColumn expands a column of nested tables, which can multiply rows because each nested table may have many rows. This second kind is what you hit after a merge, like in VLOOKUP in Power Query.

How do I expand every field without typing them out?

Pull the field names from the first record at runtime: Record.FieldNames(Table.Column(PreviousStep,"ColumnName"){0}) and pass that list as fieldNames. That way new fields get picked up without editing the formula.

List of All Power Query Functions

Related Power Query Functions / Articles: