Table.ExpandRecordColumn - Table Function

Table.ExpandRecordColumn is a Power Query M function that creates a table with a column for each field in a specified record column, optionally specifying new column names for uniqueness. The function returns a new table with expanded record columns.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

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

Argument	Attribute	Description
Table		This is the initial table that contains the column with the record you want to expand.
Column		Specifies which column in the table houses the record that needs expanding.
fieldNames		A list indicating which fields within the record should become new columns in the table.
newColumnNames	Optional	A list to rename the newly created columns. Be cautious, as these names must be unique and cannot duplicate existing column names in the table.

Description

The Table.ExpandRecordColumn function restructures a table by converting fields within a record column into separate columns. You specify which fields to convert through a list. Additionally, the function allows you to rename these new columns using an optional argument. It’s essential that any new column names are unique to prevent errors.

Examples

Let’s see the Table.ExpandRecordColumn in action.

Creating the Example Table

Imagine you have a table with two columns. The column named “Info” stores a record with multiple fields, and the other, named “Gender,” stores a text value representing a person’s sex.

You can create such a table using the #table function like so:

= #table(
  type table [Info = [Name = text, Salary = number, Birthday = date], Gender = Text.Type], 
  {{ [Name = "John Snow", Salary = 10000, Birthday = #date(1986, 1, 1)], 
     "Male"}}
)

Table.ExpandRecordColumn shows table with record in Power Query M

This simple piece of code creates a table with a record in the first column. A record is a structured value that can contain multiple fields.

Expanding a Record

If you ever need to expand these fields within a record, this is where Table.ExpandRecordColumn steps in. Let’s look at an example:

Table.ExpandRecordColumn expands record value in Power Query M

Here, the first three required arguments are used. ‘Source‘ refers to the original table, ‘Info‘ is the column that contains the record we wish to expand, and the list in argument three contains the names of the fields within the record that we want to extract as new columns.

Expand and Rename Columns

But, what if you want to alter the names of the record fields? That’s where the optional newColumnNames argument comes into play. By specifying new column names as a list in the order they appear in the third argument, you can easily rename your columns.

Table.ExpandRecordColumn assigns new names to record values in Power Query M

Avoiding Duplicate Column Names

It’s important to know that expanding new columns from a record shouldn’t create duplicate column names in the table. If you try to add the name “Gender” twice in the table (the column ‘Gender’ already exists in the table, and you try to add it from the record), you will get an error: “Expression.Error: The field ‘Gender’ already exists in the record.”

So, there you have it. When handling tables with record fields, the Table.ExpandRecordColumn function is what you need. Use it to expand and rename columns in a table.

Sample Code

Want to see all the code in action? Copy below into the advanced editor:

let
  Source = 
    #table(
      type table [Info = [Name = text, Salary = number, Birthday = date], Gender = Text.Type], 
      {{[Name = "John Snow", Salary = 10000, Birthday = #date(1986, 1, 1)], "Male"}}
    ), 
  ExpandRecord = 
    Table.ExpandRecordColumn(
      Source, 
      "Info", 
      {"Name", "Salary", "Birthday"}
    ), 
  ExpandAndRename = 
    Table.ExpandRecordColumn(
      Source, 
      "Info", 
      {"Name", "Salary", "Birthday"}, 
      {"First Name", "Income", "Bday"}
    ), 
  DuplicateColName = 
    Table.ExpandRecordColumn(
      Source, 
      "Info", 
      {"Name", "Salary", "Birthday"}, 
      {"Name", "Salary", "Gender"}
  )
in
  DuplicateColName

Other functions related to Table.ExpandRecordColumn are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/table-expandrecordcolumn