Expand a column containing a related table (Power Query)

Note: Power Query is known as Get & Transform in Excel 2016. Information provided here applies to both. To learn more, see Get & Transform in Excel 2016.

A column of complex values, such as tables, lists, records or links, can be expanded to reveal the values contained in the complex value. Complex columns that can be expanded to expose the inner elements have an expand icon ( Expand column icon ) in the column header.

In Microsoft Power Query for Excel, you use the Expand operation to bring related tables back together. For example, in an Order design, the Expand operation brings together Order_Details records that are related to an Order table, to combine order line items with each order. The Expand operation widens a subject table to include columns from a related table and expands a subject table with values from the related table. To illustrate:

A subject table has columns A and B.

Column A and B

A related table has column C.

Column A, B, and related C

The Expand operation widens a subject table to include column C and expands a subject table with related values from the data source that contains column C.


In an Order example, the Expand operation widens an Order table to include the Order_Details.ProductID, Order_Details.UnitPrice, and Order_Details.Quantity columns to bring together Order records and related Order_Details records. For a tutorial that illustrates the Expand operation, see Combine data from multiple data sources.

Expand example

Top of Page

Expand a column

  1. Click the expand icon ( Expand ) in the column header.

  2. In the column names drop-down, clear any column you are not interested in.
    Select column names

  3. Click OK.
    The table now contains a new column for each of the columns selected in step 2. The new columns are named <ComplexColumnName>.<ColumnName>.

Top of Page

Need more help?

Expand your Office skills
Explore training
Got It
Get instant Excel help
Connect to an expert now
Subject to Got It terms and conditions

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.