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 ( ) 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.

A related table has column 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.

Top of Page

Expand a column

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

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

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