Excel Power Query: Load Nested Tables into Separate Worksheets
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.
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.
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.
In Excel, select Data > Queries & Connections, right-click the relevant query, and choose Edit.
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.
In the Expand dialog box, select the columns to import, clear Use original column name as prefix if desired, and select OK.
Select Home > Close & Load or Home > Close & Load To, then choose a worksheet or another destination for the expanded query.
Create referenced queries for separate worksheet outputs
Use this approach when each nested table or filtered child result must be maintained as a separate query and worksheet.
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.

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.




