How to Transform Repeating XML Records into an Excel Table using Power Query
Question details
The user needs to convert repeating XML data groups into a structured table by extracting the start record, position, and price amount without manually repeating the transformation steps for every new dataset.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Importing and restructuring repeating XML data blocks into a flattened, analytical table format.
- Observed behavior
- The raw XML data contains repeating groups that must be logically separated, indexed, and pivoted so that each position and price correspond to the correct start record in a single row.
Ensure your XML file is properly formatted and accessible. Familiarize yourself with the Power Query Editor interface in Excel, specifically the 'Add Column' and 'Transform' tabs.
Use Indexing and Pivot in Power Query
By adding an index and grouping identifiers, you can isolate repeating XML blocks, use Fill Down for parent records, and Pivot the data into a clean table structure.
XML files often load into Power Query hierarchically. To flatten repeating 49-row blocks (or similar repeating structures), you must establish a mathematical grouping logic using an Index column. This allows you to associate nested child records (Position, Price) with their respective parent records (Start).
Open Excel, navigate to the 'Data' tab, click 'Get Data' > 'From File' > 'From XML', select your file, and click 'Transform Data' to open the Power Query Editor.
Go to the 'Add Column' tab and click 'Index Column' (starting from 0). Then, add a Custom Column using integer division (e.g., Number.IntegerDivide([Index], 49)) to create a unique ID for each repeating block.
Ensure the 'Start' field is expanded. Select the 'Start' column, go to the 'Transform' tab, and click 'Fill' > 'Down'. This populates the parent Start value across all related child rows in that specific block.
Filter out irrelevant rows so only your 'Position' and 'Price.Amount' records remain. Select your attribute column, click 'Pivot Column' on the Transform tab, choose the Values column, and set the aggregate function to 'Don't Aggregate' to flatten the table.
Try WPS Office for Seamless Data Management
If you are looking for a highly capable, lightweight, and cost-effective solution for managing complex spreadsheet data, WPS Office is an excellent alternative. Enjoy native compatibility with Excel formats and a familiar interface without the expensive subscription fees.
- 1. Download the Installer: Visit the official WPS website and download the free WPS Office suite for your operating system.
- 2. Open Your Spreadsheets: Launch WPS Spreadsheet and seamlessly open your existing Excel or XML data files.
- 3. Analyze and Structure Data: Use built-in data processing tools, advanced filtering, and PivotTables to easily manage and report on your structured data.
Frequently Asked Questions
Why does my XML data load as nested 'Table' links in Power Query?
XML files are hierarchical. Power Query represents these child nodes as nested tables. You must click the expand icon (two diverging arrows) in the column header to extract the underlying rows into your main query.
How does the 'Fill Down' feature work?
The Fill Down operation replaces null or empty values in a column with the last non-empty value directly above it. It is essential when you have a parent node (like a Date or Start record) that needs to be applied to all subsequent child records.
Can I process an entire folder of XML files at once?
Yes. By using 'Get Data' > 'From Folder' and combining the binaries, you can invoke a custom Power Query function to parse and transform all XML files in that directory simultaneously.
