Excel Power Query XML refers to using Power Query (Get & Transform Data) to import, parse, and reshape XML files directly into Excel tables. You load an XML file via Data > Get Data > From File > From XML, then use the Power Query editor to expand nested elements into columns before loading the result to a worksheet.
What Power Query Does with XML
XML is a hierarchical text format, so a single file often contains nested tags, repeated elements, and attributes. Power Query reads the file and presents it as a navigator pane where you select the node (for example, catalog or book) you want to flatten. It then converts that node into a table with columns for each child element and attribute. You can combine multiple nodes, filter rows, change data types, and merge with other queries before the final load.
How to Import an XML File in Excel
For Excel for Microsoft 365 on Windows, the steps are:
- Go to Data > Get Data > From File > From XML.
- Browse to your .xml file and click Import.
- In the Navigator dialog, select the node that contains the records you want (e.g.,
book). The preview shows a table. - Click Transform Data to open the Power Query Editor.
- In the editor, use Choose Columns, Expand (the arrows in column headers), and Change Type to shape the data.
- Click Close & Load to place the result in a worksheet, or Close & Load To to choose a destination.
On a Mac, the path is Data > Get Data > From File > From XML (the ribbon wording may be slightly condensed, but the feature exists in Excel for Microsoft 365 for Mac). Excel for the web does not support Power Query import from XML files; you would need to open the desktop version.
Understanding the XML Structure Before You Start
XML files have a tree structure. For example, a simple catalog might look like:
<catalog>
<book id="1">
<title>Excel Basics</title>
<author>Jane Doe</author>
<price>29.99</price>
</book>
<book id="2">
<title>Power Query Guide</title>
<author>John Smith</author>
<price>39.99</price>
</book>
</catalog>
When you select the book node, Power Query creates a table with columns for id, title, author, and price. If a node has nested children (like <author><name>...</name></author>), you will see a column with the word Table in each cell. Click the expand icon (double arrow) in the column header to bring those sub-fields into separate columns.
Common Problems and How to Fix Them
Users often run into these issues:
- Blank rows or missing data: This happens when some elements are optional. Use Remove Rows > Remove Blank Rows or filter out nulls in the column.
- Attributes not showing: Attributes (like
id="1") appear as columns only when you expand the node. If you don’t see them, check the Attributes section in the navigator or use the Expand button on the column that contains the record. - Data types are all text: Power Query often imports everything as text. Select the column, then use Home > Data Type to change to whole number, decimal, date, etc.
- File is too large: Power Query can handle large files, but it may be slow. Use Filter early to reduce rows, and avoid loading unnecessary columns.
- Cannot find the XML option: If you are on an older Excel version (before 2016), Power Query is an add-in. In Excel 2016 and later it is built-in. For Excel 2013, you need to install the Power Query add-in from Microsoft’s site.
Comparing XML Import Methods in Excel
Excel offers several ways to handle XML, and Power Query is not always the best choice:
- Power Query (Get & Transform): Best for complex, nested, or repeated XML structures. It is non-destructive (original file unchanged) and refreshable.
- XML Map (Developer tab): Use when you need to bind XML elements to specific cells and keep a live link for exporting data back to XML. It requires the Developer tab and works only with a schema. It is less flexible for reshaping.
- Open XML directly: Excel can open an .xml file as a table, but it often flattens only the first level and may not handle nested data well. This is a quick one-off method but not refreshable.
For recurring imports or complex transformations, Power Query is generally the most robust.
Refreshing and Automating XML Imports
Once you load an XML file with Power Query, the connection is saved in the workbook. To update with new data, simply replace the original file (or change the file path in the query) and click Data > Refresh All. You can also set the query to refresh automatically at intervals: go to Data > Queries & Connections, right-click the query, select Properties, and under Refresh control check Refresh every and enter minutes. For scheduled refresh in Power BI or Excel Online, you would need a gateway, but in desktop Excel it is manual or timer-based.
Frequently Asked Questions
Q: Can I import multiple XML files at once?
Yes. Use Data > Get Data > From File > From Folder, then combine the XML files. Power Query will show a list of files; you can write a custom function to parse each file and append the results.
Q: Why do I see a column with “Record” values instead of data?
That happens when a node contains a single nested object rather than a list. Click the expand icon on that column header and choose the fields you want to bring out.
Q: Does Power Query handle XML attributes?
Yes. Attributes are imported as regular columns when you expand the element. For example, <book id="1"> gives a column named id with value 1.
Q: Can I edit the XML file and refresh without redoing everything?
Absolutely. Power Query stores the file path and query steps. After you modify the XML file, click Refresh and the query re-runs, applying all your transformations.
Q: Is Power Query available in Excel 2016?
Yes, Power Query is built into Excel 2016 and later. In Excel 2013 it is a free add-in. In Excel 2010 it is also available as an add-in, but support is limited.
Alternatives to Power Query for XML
If Power Query is unavailable or you prefer a formula-based approach, you can use the FILTERXML function (Excel 2013 and later on Windows) to extract specific values from an XML string. For example, with the XML text in cell A1, =FILTERXML(A1, "//book/title") returns an array of all titles. However, FILTERXML does not handle large files well and is not refreshable from a file path. For complex transformations, Power Query remains the recommended tool. Another alternative is to use VBA with the Microsoft XML DOM library to parse XML programmatically, but that requires coding and is less user-friendly.
Related
- Excel Learning Center Jacksonville NC: Classes & Training
- Excel Inventory Tracking Template Walkthrough
- Excel Jet Forecast? How to Build One
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Delete a Page in Word on PCTo delete a page in Word on a PC, select the page’s contents with Go To and press…
- Can You Password Protect an Excel Spreadsheet?Yes. In Excel for Microsoft 365 on Windows, you can require a password to open a workbook by…
- How to Open the Trust Center in Excel for Microsoft 365In Excel for Microsoft 365 on Windows, open the Trust Center by selecting File > Options > Trust…
- Snowflake SPLIT_PART: Syntax, Examples & TipsIn Snowflake SQL, SPLIT_PART(string, delimiter, partNumber) returns one piece of a string separated by a specified delimiter. The…
- Naming a Cell Range in Google Sheets: Steps, Uses, and TipsTo name a cell range in Google Sheets, select the cells, choose Data > Named ranges, enter a…
- How to Delete Only Visible Rows in Excel Without Hidden DataTo delete only visible rows in Excel, filter or hide the rows you want to keep, select the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.