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 tableDescription
Expands tables in table[column] into multiple rows and columns. columnNames is used to select the columns to expand from the inner table. Specify newColumnNames to avoid conflicts between existing columns and new columns.
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]
} )
*/
Related articles
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
Related functions
Other functions related to Table.ExpandTableColumn are:
