Expand a column containing a related table (Power Query)

Important   This article applies to Power Query, a data analysis feature available for Excel that lets you discover, combine and refine data. You may need to enable Power Query in Excel. You can also download and install the most recent version of Power Query for Excel, which automatically enables it. Power Query technology is also built into the Power BI Designer, which is a stand-alone report authoring and data transformation tool for Power BI.

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

Applies To: Excel 2010, Excel 2013

Was this information helpful?

Yes No

How can we improve it?

255 characters remaining

To protect your privacy, please do not include contact information in your feedback. Review our privacy policy.

Thank you for your feedback!

Change language