Most Excel trainers tell you to open XML files with Notepad first, then copy-paste into Excel. That’s dangerous. You’ll lose nested structure, misalign attributes, and break dates like '2024-03-15T08:22:17'. XML isn’t plain text—it’s hierarchical data wearing a disguise.
The Setup
You get an XML file from your ERP system—orders_2024_q1.xml. It contains 9 customer orders, each with <order_id>, <customer_name>, <items> (with nested <sku>, <qty>, <price>), and <ship_date>.
| Raw XML Snippet (first 3 orders) |
|---|
| <orders><order id="ORD-7821"><customer_name>Sarah Chen</customer_name><ship_date>2024-03-15</ship_date><items><item sku="A77X" qty="2" price="149.99"/><item sku="B33Y" qty="1" price="89.50"/></items></order><order id="ORD-7822"><customer_name>Miguel Ruiz</customer_name><ship_date>2024-03-16</ship_date><items><item sku="C99Z" qty="5" price="24.75"/></items></order><order id="ORD-7823"><customer_name>Anya Petrova</customer_name><ship_date>2024-03-17</ship_date><items><item sku="A77X" qty="1" price="149.99"/><item sku="D11W" qty="3" price="32.00"/></items></order></orders> |
The Challenge
You need one flat table in Excel: one row per line item—not per order. So ORD-7821 becomes two rows (A77X + B33Y). That means exploding nested <item> elements while preserving parent order_id, customer_name, and ship_date.
This fails if you use Data → From Text/CSV. Excel treats the whole XML as one string. And Power Query’s default XML parser flattens everything into columns like orders.order.customer_name and orders.order.items.item.sku—but won’t auto-repeat parent values across child rows.
The real trap? Using "XML Source" under Developer tab. It forces schema mapping—and if your XML has inconsistent nesting (some orders have 1 item, others have 4), the map breaks silently at row 7.
Walking Through It
Step 1: Open Excel. Go to Data → Get Data → From File → From XML. Navigate to orders_2024_q1.xml. Click Import. Don’t click Load—click Transform Data.
Step 2: In Power Query Editor, you’ll see one column named orders with a single cell containing a record. Click the expand icon (⇆) next to it. Check “Use original column name as prefix”. Click OK.
Step 3: Now you see orders.order. Click its expand icon. Uncheck “Use original column name as prefix”. Select only id, customer_name, ship_date, and items. Click OK.
Step 4 (the counterintuitive part): Right-click items → “Expand to New Rows”. Do not expand the items column yet. This step promotes each <item> to its own row—and automatically repeats id, customer_name, and ship_date down the new rows. Most people skip this and try to expand first—then lose the parent context.
Step 5: Now click the expand icon on the new items column. Select sku, qty, and price. Uncheck “Use original column name as prefix”. Click OK.
Step 6: Rename columns: id → order_id, customer_name → customer, ship_date → ship_date, sku → product_sku, qty → quantity, price → unit_price. Then go to Home → Close & Load.
That’s it. No VBA. No third-party tools. Alt+D+F+P opens Power Query faster than navigating menus.
| Before (Power Query preview, after Step 4) |
|---|
| order_id: ORD-7821 customer: Sarah Chen ship_date: 2024-03-15 items: [Record] order_id: ORD-7821 customer: Sarah Chen ship_date: 2024-03-15 items: [Record] order_id: ORD-7822 customer: Miguel Ruiz ship_date: 2024-03-16 items: [Record] |
| After (Step 6 result in Excel) |
|---|
| A1: order_id | B1: customer | C1: ship_date | D1: product_sku | E1: quantity | F1: unit_price A2: ORD-7821 | B2: Sarah Chen | C2: 2024-03-15 | D2: A77X | E2: 2 | F2: 149.99 A3: ORD-7821 | B3: Sarah Chen | C3: 2024-03-15 | D3: B33Y | E3: 1 | F3: 89.50 A4: ORD-7822 | B4: Miguel Ruiz | C4: 2024-03-16 | D4: C99Z | E4: 5 | F4: 24.75 A5: ORD-7823 | B5: Anya Petrova | C5: 2024-03-17 | D5: A77X | E5: 1 | F5: 149.99 A6: ORD-7823 | B6: Anya Petrova | C6: 2024-03-17 | D6: D11W | E6: 3 | F6: 32.00 |
The Result
| order_id | customer | ship_date | product_sku | quantity | unit_price |
|---|---|---|---|---|---|
| ORD-7821 | Sarah Chen | 2024-03-15 | A77X | 2 | 149.99 |
| ORD-7821 | Sarah Chen | 2024-03-15 | B33Y | 1 | 89.50 |
| ORD-7822 | Miguel Ruiz | 2024-03-16 | C99Z | 5 | 24.75 |
| ORD-7823 | Anya Petrova | 2024-03-17 | A77X | 1 | 149.99 |
| ORD-7823 | Anya Petrova | 2024-03-17 | D11W | 3 | 32.00 |
| ORD-7824 | James Wilson | 2024-03-18 | B33Y | 4 | 89.50 |
| ORD-7825 | Linh Tran | 2024-03-19 | C99Z | 2 | 24.75 |
| ORD-7826 | Diego Morales | 2024-03-20 | D11W | 1 | 32.00 |
| ORD-7827 | Fatima Al-Mansoori | 2024-03-21 | A77X | 3 | 149.99 |
What Could Go Wrong
Mistake 1: Skipping “Expand to New Rows” on the items column. You’ll get one row per order—but all item data crammed into a single cell like [Record], [Record]. Excel can’t split that without manual parsing. Fix: Right-click → “Expand to New Rows” before expanding the record.
Mistake 2: Loading XML with missing root node or malformed tags. Power Query throws “Expression.Error: The key didn't match any rows in the table.” That means your XML lacks a top-level wrapper (e.g., no <orders>) or has unescaped ampersands (& instead of &). Fix: Open XML in Notepad++ first. Search for &, <, >. Replace bare & with &.
Mistake 3: Date columns showing as text (e.g., “2024-03-15”) instead of serial numbers. Excel doesn’t auto-convert them. You’ll get 0 in SUMIFS. Fix: In Power Query, select the ship_date column → Transform tab → Date → Date. Or in Excel: select column → Ctrl+1 → Category → Date.
Next step: Save this query. Next time you get orders_2024_q2.xml, right-click the query in Data → Queries & Connections → Refresh. Done.
