Power Query Problems

How to Fix Excel Power Query Showing [Table] Instead of Data

WPS Content Manager Sep 30, 2026 869 views

Question details

The user is attempting to load data using Excel Power Query, but the output displays the text [Table] instead of the actual data rows, especially after a date filter is applied.

How to Fix Excel Power Query Showing [Table] Instead of Loaded Data

Product: Microsoft Excel
Device & OS: not provided
Scenario: Filtering and transforming data in Power Query to load into an Excel workbook worksheet.
Observed behavior: The query editor previews the data correctly, but when loading to the worksheet or viewing specific columns, it shows [Table] (indicating nested tables) instead of the extracted records, and loading into the workbook fails.

Before you start

Before modifying your Power Query steps or altering query connections, create a backup copy of your Excel workbook to ensure you can revert if your data structure changes unexpectedly.

Solution 1Recommended

Expand Nested Tables in Power Query Editor

Use this method to extract the underlying data hidden inside nested tables, which appears simply as the word [Table] in your query columns.

In Power Query, the presence of [Table] indicates that a single cell contains an entire nested table. This frequently happens when merging queries, grouping data, or importing from complex structures like folders or JSON files. You must explicitly expand these tables to expose the rows and columns they contain.

Open Power Query Editor

Navigate to the 'Data' tab in the Excel ribbon and click on 'Queries & Connections'. Double-click your problematic query to open the Power Query Editor.

Locate the Expand Button

Find the column header that displays the [Table] values. In the top-right corner of that column header, click the Expand icon (two arrows pointing in opposite directions).

Select Columns to Expand

A drop-down menu will appear. Check the boxes for the specific columns you want to extract from the nested table. Uncheck 'Use original column name as prefix' if you prefer cleaner column headers, then click 'OK'.

Load the Data

Once the data displays correctly in the preview, click 'Close & Load' in the top-left corner of the Home tab to load the expanded records into your worksheet.

Expand Nested Tables in Power Query Editor

Expansion Successful: Expanding the nested tables converts the [Table] entries into standard flat rows, allowing Excel to display the actual data natively.

Solution 2

Verify Query Load Destinations and Settings

Check your query load settings to ensure the transformed data is being directed to a visible worksheet rather than just saving as a background connection.

Solution 3

Troubleshoot Date Filter Applied Steps

Since the issue often triggers after applying a date filter, review the applied steps to fix any data type mismatches causing the query to fail.

Free Microsoft Office alternative

Experience Seamless Data Management with WPS Office

If Microsoft Excel's Power Query is causing persistent loading issues or nested table errors, consider switching to WPS Office. It provides a highly compatible, lightweight, and completely free alternative to Microsoft Office, complete with powerful spreadsheet tools and a familiar interface for smooth data processing.

1. Download and Install: Visit the official WPS website to download the free WPS Office suite and follow the simple installation prompts.
2. Open Your Spreadsheet: Launch WPS Spreadsheets and directly open your existing Excel workbooks seamlessly without worrying about format corruption.
3. Manage Data Easily: Use the familiar data filtering, sorting, and pivot table tools to analyze your data quickly and efficiently.

Fully compatible with Microsoft Excel (.xlsx, .xls, .csv) formatsLightweight installation with fast performance for large datasetsIntuitive and familiar interface that requires zero learning curveCompletely free to use with built-in PDF tools and premium templates

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Power Query show [Table] in a column?

The text [Table] indicates that the column contains nested tables. This happens when data is grouped, when queries are merged, or when importing complex structures like folders. You must use the Expand button in the column header to extract the actual rows.

Why does my query fail to load after applying a date filter?

Applying a date filter can cause a load failure if the column's data type is mixed or incorrectly set as text instead of a Date format. Ensure the column is explicitly formatted as 'Date' in Power Query before applying the filter.

How do I fix a Power Query that won't load into a worksheet?

Check your query's 'Load To' settings by right-clicking the query in the Queries & Connections pane. Ensure it is set to load to a 'Table' in a worksheet rather than 'Only Create Connection'. Also, check the query editor for any step errors preventing data generation.

Can I extract all rows from a nested table in Power Query?

Yes. Clicking the two-arrow Expand icon on the nested table's column header allows you to select which columns to extract. Expanding the table will multiply the existing rows to match the number of records inside the nested table.

Excel & Data Problems