Table.ExpandTableColumn Function in Power Query

The Table.ExpandTableColumn function in Power Query is used to expand a table-type column in a table into multiple columns. When we have a column that contains nested tables, this function helps us extract specific fields from those tables and flatten them into columns in the main table.

Syntax

Table.ExpandTableColumn(
table as table,
column as text,
columnNames as list,
optional newColumnNames as nullable list) as table

The followings are parameters in the function:

table: The input table containing the nested table column to expand.
column: The name of the column (as text) that contains the nested tables.
columnNames: A list of column names from the nested table that we want to extract.
newColumnNames: It is an optional parameter. A list of new column names for the extracted fields from the nested table column.

Example:

Power Query M

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

The output of the above code is shown below:

Table.ExpandTableColumn function in Power Query

Let’s expand the table-type column.

Power Query M

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

The output of the above code is shown below:

Previous Next