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:

Now in the following code we are expanding the record columns and include the columns 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"}, 
    {"Customer State", "Customer Country"}
  )
in
  Return

The output of the above code is shown below:

Table.ExpandRecordColumn function in Power Query

Previous Next