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

Split the list column [Name].

Table.ExpandListColumn( 
    Table.FromRecords( {[Name = {"Bob", "Jim", "Paul"}, Discount = .15]} ),
    "Name"
 )

 /* Output: 
Table.FromRecords( {
    [Name = "Bob", Discount = 0.15],
    [Name = "Jim", Discount = 0.15],
    [Name = "Paul", Discount = 0.15]
} )
 */ 

Split the nested table column [Components].

Table.ExpandListColumn(
    #table(
        {"Part", "Components"},
        {
            {"Tool", #table({"Name", "Quantity"}, {{"Thingamajig", 2}, {"Widget", 3}})}
        }
    ),
    "Components"
)

/* Output: 
Table.FromRecords( {
    [Part = "Tool", Components = [Name = "Thingamajig", Quantity = 2]],
    [Part = "Tool", Components = [Name = "Widget", Quantity = 3]]
} )
*/

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