Table.ExpandTableColumn

Updated on

Table.ExpandTableColumn is a Power Query M function that expands tables in a specified column into multiple rows and columns, selecting columns to expand from the inner table and specifying new column names to avoid conflicts. The function returns a new table with expanded table columns.

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

Syntax

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

Description

Table.ExpandTableColumn expands tables in table[column] into rows and columns. columnNames selects columns to expand; newColumnNames avoid conflicts.

Examples

Expand table columns in [a] in the table ( {[t = {[a=1, b=2, c=3], [a=2,b=4,c=6]}, b = 2]} ) into 3 columns [t.a], [t.b] and [t.c].

Table.ExpandTableColumn( 
    Table.FromRecords( {
        [
            t = Table.FromRecords( {
                [a = 1, b = 2, c = 3],
                [a = 2, b = 4, c = 6]
            } ),
            b = 2
        ]
    } ),
    "t",
    {"a", "b", "c"},
    {"t.a", "t.b", "t.c"}
 )

 /* Output: 
Table.FromRecords( {
    [t.a = 1, t.b = 2, t.c = 3, b = 2],
    [t.a = 2, t.b = 4, t.c = 6, b = 2]
} )
 */ 

Learn more about Table.ExpandTableColumn in the following articles:

  • Join Types in Power Query
    Power Query has different join types for merging queries. Mastering them allows you to easily retrieve new values, keep relevant ones or discard them.. » Read more

Other functions related to Table.ExpandTableColumn are:

BI Gorilla Youtube Channel

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