Power Query Problems

Excel Power Query: Load Nested Tables into Separate Worksheets

Maira MehtabMaira Mehtab Nov 12, 2026 868 views

Question details

The user needs to expand nested Power Query tables or load their contents into separate Excel worksheets.

Product
Microsoft Excel Power Query
Device & OS
not provided
Scenario
A query contains nested tables, and the user wants to combine or separate their contents when loading the results to Excel.
Observed behavior
Nested table cells display the text “Table” instead of automatically opening each nested table on its own worksheet.
Before you start

Open the query in Power Query Editor and identify the column whose cells display “Table.” Confirm whether you want to combine the nested rows with the parent query or keep each nested result as a separate query.

Solution 1Recommended

Expand the nested table column in Power Query

Use this approach when the nested rows should be combined with the parent query before loading the final result.

Power Query does not automatically create a worksheet for every nested table. Expanding the nested column brings selected fields from each child table into the parent query.

1
Open the query editor

In Excel, select Data > Queries & Connections, right-click the relevant query, and choose Edit.

2
Expand the nested column

In Power Query Editor, locate the column whose cells show “Table,” then select the expand button with the two-arrow icon in that column header.

3
Choose the child fields

In the Expand dialog box, select the columns to import, clear Use original column name as prefix if desired, and select OK.

4
Load the combined result

Select Home > Close & Load or Home > Close & Load To, then choose a worksheet or another destination for the expanded query.

Expected result: The cells that previously displayed “Table” are replaced with the selected child-table values in the parent query output.
Free Microsoft Office alternative

Use WPS Office for Excel-Compatible Query Results and Worksheet Management

WPS Office is a free, lightweight Microsoft Office alternative for opening, reviewing, editing, and organizing Excel-compatible workbooks. Although Power Query itself is a Microsoft Excel feature, WPS makes it easy to work with the resulting worksheets and migrate between familiar spreadsheet formats.

Open and edit Excel-compatible .xlsx and .xls workbooks with a familiar spreadsheet interface.Review expanded query results and organize separate worksheet outputs efficiently.Use a lightweight office suite for everyday spreadsheet editing and formatting.Maintain compatibility when sharing workbooks with Microsoft Excel users.
microsoft office alternative - wps office

Frequently Asked Questions

Why does Power Query show “Table” in a cell?

The cell contains a nested table value. Power Query displays the value as “Table” until you select the expand icon in the column header and choose which child-table fields to add to the parent query.

Can Power Query automatically load every nested table to a separate worksheet?

No. Power Query does not automatically create one worksheet for every nested table. Expand the nested column to combine the data, or create referenced queries and use Home > Close & Load To for separate worksheet outputs.

How do I expand only specific columns from a nested table?

Open the query in Power Query Editor, select the two-arrow expand button in the nested-table column header, choose only the required fields in the Expand dialog box, and select OK.

What is the difference between Duplicate and Reference in Power Query?

Duplicate copies the query steps into an independent query, while Reference creates a query that starts from the result of the original query. Use Reference when separate worksheet outputs should remain connected to the parent query.

Where can I choose a worksheet as the destination for a query?

In Power Query Editor, select Home > Close & Load To, choose Table, select New worksheet or an existing worksheet, and then select Load.