Table.ExpandRecordColumn Function in Power Query

The Table.ExpandRecordColumn function expands a column of records into columns with each of the values.

Syntax

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

The function has the following parameters:

table: This is the original table that contains the column with the record we want to expand.
column: It specifies the column name in the original table which contains the records which we want to expand.
fieldNames: It specifies the list of columns from the record which want to select from the record. The column names are specified in the list of columns in text.
newColumnNames: It is an optional parameter. It specifies the list to rename the newly created columns. Please note the column names must be unique and cannot duplicate existing column names in the table.

Example: The following is the code shows the table with many columns CustomerID, Name and Address. In this table the Address column is a column that contains records. The records contain the two columns State and Country.

Power Query M

let
  MyTable = Table.FromRecords(
    {
      [CustomerID = 1, Name = "Ashish", Address = [State = "Haryana", Country = "India"]], 
      [CustomerID = 2, Name = "Katrina", Address = [State = "Uttar Pradesh", Country = "India"]], 
      [CustomerID = 3, Name = "Alia", Address = [State = "Bihar", Country = "India"]], 
      [CustomerID = 4, Name = "Vicky", Address = [State = "TamilNadu", Country = "India"]], 
      [CustomerID = 5, Name = "Mohini", Address = [State = "Delhi", Country = "India"]], 
      [CustomerID = 6, Name = "Meenakshi", Address = [State = "Haryana", Country = "India"]], 
      [CustomerID = 7, Name = "Esha", Address = [State = "Uttar Pradesh", Country = "India"]], 
      [CustomerID = 8, Name = "Anjali", Address = [State = "Maharashtra", Country = "India"]]
    }
  )
in
  MyTable

The output of the above code is shown below:

Table.ExpandRecordColumn function in Power Query

Now in the following code, we are expanding the record column and include the columns State and Country from the record in our table.

Power Query M

let
  MyTable = Table.FromRecords(
    {
      [CustomerID = 1, Name = "Ashish", Address = [State = "Haryana", Country = "India"]], 
      [CustomerID = 2, Name = "Katrina", Address = [State = "Uttar Pradesh", Country = "India"]], 
      [CustomerID = 3, Name = "Alia", Address = [State = "Bihar", Country = "India"]], 
      [CustomerID = 4, Name = "Vicky", Address = [State = "TamilNadu", Country = "India"]], 
      [CustomerID = 5, Name = "Mohini", Address = [State = "Delhi", Country = "India"]], 
      [CustomerID = 6, Name = "Meenakshi", Address = [State = "Haryana", Country = "India"]], 
      [CustomerID = 7, Name = "Esha", Address = [State = "Uttar Pradesh", Country = "India"]], 
      [CustomerID = 8, Name = "Anjali", Address = [State = "Maharashtra", Country = "India"]]
    }
  ), 
  Return = Table.ExpandRecordColumn(
    MyTable, 
    "Address", 
    {"State", "Country"}
  )
in
  Return

The output of the above code is shown below:

Without specifying the optional new column names, the columns names added in the table is the same as the original table.

Expand and Rename Columns By specifying new column names as a list in the order they appear in the third argument, we can easily rename our columns.

Example: Specify the new column names as specified in the table.

Power Query M

let
  MyTable = Table.FromRecords(
    {
      [CustomerID = 1, Name = "Ashish", Address = [State = "Haryana", Country = "India"]], 
      [CustomerID = 2, Name = "Katrina", Address = [State = "Uttar Pradesh", Country = "India"]], 
      [CustomerID = 3, Name = "Alia", Address = [State = "Bihar", Country = "India"]], 
      [CustomerID = 4, Name = "Vicky", Address = [State = "TamilNadu", Country = "India"]], 
      [CustomerID = 5, Name = "Mohini", Address = [State = "Delhi", Country = "India"]], 
      [CustomerID = 6, Name = "Meenakshi", Address = [State = "Haryana", Country = "India"]], 
      [CustomerID = 7, Name = "Esha", Address = [State = "Uttar Pradesh", Country = "India"]], 
      [CustomerID = 8, Name = "Anjali", Address = [State = "Maharashtra", Country = "India"]]
    }
  ), 
  Return = Table.ExpandRecordColumn(
    MyTable, 
    "Address", 
    {"State", "Country"}, 
    {"Customer State", "Customer Country"} 
  )
in
  Return

The output of the above code is shown below:

Previous Next