Table.ExpandTableColumn - Table Function

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

Argument	Attribute	Description
table		The table containing the nested table objects.
column		The name of the column that contains the table objects.
columnNames		The column names you want to expand from the table object.
newColumnNames	optional	Custom names for the expanded columns. Provide these in the same order as the columns appear in the columnNames argument.

Description

Table.ExpandTableColumn allows you to extract and expand data from nested tables stored within a column of a main table. By specifying the desired columns from the nested tables, it adds these as new columns to the main table. During this process, each row of the main table is duplicated for every row in the corresponding nested table, ensuring a proper relational structure.

Additionally, you can provide custom names for the expanded columns using the optional newColumnNames parameter. This helps avoid naming conflicts with existing columns or creates more descriptive headers for the expanded data.

Examples

Suppose you have the following table, called Source, which contains nested tables in the Variants column:

Table.ExpandTableColumn dataset containing table values in Power Query M

Expanding Nested Tables Using the UI

The Variants column contains nested tables with two columns: Variant and Segment. Our goal is to expand the Variants column into separate columns in the main table.

The simplest way to expand nested tables is by using Power Query’s interface. In the Variants column header, click the Expand button (displayed as two small arrows). This action lets you select which columns from the nested table you want to expand into the main table. After making your selections, click OK.

The Power Query M code generated for this operation looks like this:

Table.ExpandTableColumn(
  Source, 
  "Variants", 
  { "Variant", "Segment" }, 
  { "Variant", "Segment" }
)

The output of this operation is a table that contains two new columns. Here’s what the result looks like:

Table.ExpandTableColumn expands table objects from column in Power Query M

Notice how each row of the original table is duplicated for every row in the nested table. For example, the Laptop row is repeated three times, once for each Variant in its nested table.

Handling Errors: Naming Conflicts in Expanded Columns

The third argument of Table.ExpandTableColumn (the list of columns to expand) is mandatory. However, the fourth argument (the names of the new columns) is optional. If you omit the fourth argument, Power Query will use the column names from the nested table as the default.

This can cause errors if a column name in the nested table matches an existing column name in the main table. For example, if the main table already has a column called Variant, expanding a column with the same name would result in a conflict.

Renaming Expanded Columns

To avoid naming conflicts or customize your output, you can specify unique names for the new columns using the fourth argument. For instance, if you want to rename Variant to Version and Segment to Category, your code would look like this:

Table.ExpandTableColumn(
  Source, 
  "Variants", 
  { "Variant", "Segment" }, 
  { "Version", "Category" }
)

In this example, the Variant column is renamed to Version, and the Segment column is called Group.

Complete Example with M Code

Below is a full Power Query M script demonstrating how to expand the Variants column in the Source table:

let
  Source = 
    #table(
    type table [ Product = text, Category = text, Variants = table [Variant = text, Segment = text ] ],
    {
        { "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"}}) }
    }
),
    #"Expanded Variants" = Table.ExpandTableColumn(Source, "Variants", {"Variant", "Segment"}, {"Variant", "Segment"})
in
    #"Expanded Variants"

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:

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