How to Expand Table Values When Excel Web Imports XML Data
Question details
Users importing XML data directly from websites into Excel find that some fields display the word 'Table' rather than the actual underlying data values.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Importing XML data directly from a web URL into an Excel spreadsheet.
- Observed behavior
- Certain data fields are formatted as complex nested objects, showing up as 'Table', 'List', or 'Record' in the cells instead of displaying the raw data values.
Ensure you have an active internet connection to refresh the web data and that your version of Excel supports Get & Transform Data (Power Query).
Expand Complex Columns in Power Query Editor
Use the built-in Power Query Editor to manually expand columns containing nested tables, lists, or records into individual rows or fields.
When Excel imports structured data like XML directly from the web, nested data structures are often bundled into complex objects to preserve their hierarchy. Power Query provides an easy built-in tool to extract these underlying values.
In Excel, select your imported data table, navigate to the 'Data' tab on the ribbon, and click 'Get Data' > 'Launch Power Query Editor'. Alternatively, double-click the query in the Queries & Connections pane.
Scroll through your dataset in the editor to find the specific column where the cells display the word 'Table' (or 'List'/'Record') in green text.
Click the 'Expand' icon (two arrows pointing in opposite directions) located in the upper-right corner of that column's header.
In the drop-down menu, choose which nested fields you want to extract. Uncheck 'Use original column name as prefix' if you prefer shorter column names, then click 'OK'.
Once the underlying values are visible, click 'Close & Load' on the Home tab to apply your changes and output the expanded data to your Excel worksheet.
A Lightweight and Powerful Alternative for Spreadsheets
If you frequently encounter complex data import issues with Microsoft Excel, consider switching to WPS Office. It provides a highly compatible, free, and lightweight spreadsheet solution that handles standard data formats with ease.
- 1. Download WPS Office: Visit the official WPS website and download the free installer for your operating system.
- 2. Install the Software: Run the installer and follow the straightforward on-screen instructions to set up WPS Office on your device.
- 3. Open Your Spreadsheets: Launch WPS Spreadsheets and open your existing .xlsx or .xml files to start editing immediately.
Frequently Asked Questions
Why does Excel show 'Table' instead of my XML data?
When importing structured data like XML or JSON, nested elements containing multiple sub-items are bundled into objects. Power Query represents these as 'Table', 'Record', or 'List' to prevent data loss until you explicitly choose how to expand them.
How do I extract data from a 'Record' in Power Query?
Similar to expanding a table, click the Expand icon on the Record column header. This will open a menu allowing you to select the specific fields within the record to pull into new distinct columns.
Can I automatically expand all complex columns during import?
There isn't a native one-click setting to expand all nested columns automatically upon web import. You must open the Power Query Editor and manually click the expand icon for each complex column before loading the data.
Why does opening a local XML file behave differently than importing from the web?
Opening a local XML file often triggers Excel's legacy XML parser, which flattens the data structure automatically. Web imports utilize the newer Power Query engine, which strictly maintains the hierarchical structure of the data and requires manual expansion.
