Table.ExpandListColumn

Table.ExpandListColumn is a Power Query M function that splits a column containing lists into separate rows for each value, duplicating other column values in each new row. The function returns a new table with expanded rows.

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

Syntax

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

Description

The Table.ExpandListColumn function, given a `table` where `column` contains a list of values, splits the list into a row for each value. Values in the other columns are duplicated in each new row created. This function can also expand nested tables by treating them as lists of records.

Examples

Let’s take a look at two useful scenarios for Table.ExpandListColumn: expanding list values and expanding table values.

Expanding List Values

Suppose you’re working with a table where one column contains list values. For example, a table named Source includes a column called Variants that holds lists.

 To make these values more accessible, you can expand them into individual rows.

You can easily do that with the Power Query user interface: click the arrow icon in the column header and select Expand to New Rows. This action generates the following M code:

Table.ExpandListColumn( Source, "Variants" )

This tells the engine to expand the list values in the Variants column of the Source table. After expansion, the table looks like this:

To try this example yourself, you can use the following code:

let
  Source = 
    #table(
    type table [ Product = text, Category = text, Variants = { text } ],
    {
        { "Laptop",      "Electronics", { "Gaming", "Ultrabook", "Workstation" } },
        { "Smartphone",  "Electronics", { "Android", "iOS", "Budget" } },
        { "Chair",       "Furniture",   { "Office", "Gaming", "Recliner" } },
        { "Shoes",       "Apparel",     { "Sneakers", "Formal", "Running" } }
    }
),
    ExpandVariants = Table.ExpandListColumn(Source, "Variants")
in
    ExpandVariants

Expanding Table Values

Although the name Table.ExpandListColumn suggests it works only with lists, it can also expand table values. When a column contains tables, expanding it duplicates each row in the main table for every row in the nested table and returns records.

Suppose you have the following Source table:

The preview shows that the table value in row 4 contains two columns and four rows. If you want to expand all rows and columns, the default expand operation uses the Table.ExpandTableColumn function. However, you can also expand each row in the nested table object and return all column values as a record.

To do that you can use the following code:

Table.ExpandListColumn( Source, "Variants" )

The expanded table includes all rows and columns from the nested tables:

To make all values visible, you could then use the Table.ExpandRecordColumn function. However, that would involve unnecessary steps, since the Table.ExpandTableColumn function can achieve the list and record expansion in a single step.

You can try these examples yourself with the following code:

let
  Source = 
    #table(
    type table [ Product = text,       Category = text,       Variants = table ],
    {
        { "Laptop",      "Electronics", #table({"Variant", "Segment"}, {{"Gaming", "High-End"}, {"Ultrabook", "Portable"}, {"Workstation", "Professional"}}) },
        { "Smartphone",  "Electronics", #table({"Variant", "Segment"}, {{"Android", "Open Source"}, {"iOS", "Premium"}, {"Budget", "Affordable"}}) },
        { "Chair",       "Furniture",   #table({"Variant", "Segment"}, {{"Office", "Ergonomic"}, {"Gaming", "Stylish"}, {"Recliner", "Comfort"}}) },
        { "Shoes",       "Apparel",     #table({"Variant", "Segment"}, {{"Sneakers", "Casual"}, {"Formal", "Business"}, {"Running", "Athletic"}}) }
    }
),
    ExpandVariants = Table.ExpandListColumn(Source, "Variants")
in
    ExpandVariants

Related functions

Other functions related to Table.ExpandListColumn are:

• Table.ExpandRecordColumn
• Table.ExpandTableColumn

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