Table.ExpandListColumn Function in Power Query

The Table.ExpandListColumn function in Power Query is used to expand a list-type column in a table into multiple rows, where each row represents one item from the list. At the time of creating multiple rows, in the other columns the values are repeated.

Syntax

Table.ExpandListColumn(table as table, column as text) as table

Example:

Power Query M

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

The output of the above code is shown below:

Table.ExpandListColumn function in Power Query

Let’s expand the list-type column “Address” in the table.

Power Query M

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

The output of the above code is shown below:

Previous Next