Expand a column containing a related table

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 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 Top of Page

Applies to:
Excel 2013, Excel 2010