Import Data from XML Using Power Query or Get & Transform

Power Query or Get & Transform (In Excel 2016) lets you perform a series of steps to transform your Excel data. But what if your data source is not in your Excel spreadsheet? It’s very common nowadays to get data imported from a company’s accounting or sales system in the XML format.  If it’s inside a XML (extensible markup language) file, it’s very easy to import data from xml and right into Power Query!  

Key Takeaways

• Power Query Makes XML Import Simple – Easily connect to and transform XML data into structured tables with just a few clicks.

• Flexible Data Shaping Options – Filter, expand, and reshape nested XML elements to match your analysis needs.

• Supports Both Local and Web XML Files – You can import XML files from your computer or from an online URL.

• Refreshable Data Source – Once set up, your XML query can be refreshed automatically to bring in updated data.

• Ideal for Repetitive Data Imports – Great for recurring tasks like pulling data from system exports or online feeds.

Our Data Set

Let’s suppose you have this set of data from the xml file:

How to Import Data from XML Using Power Query or Get & Transform

STEP 1:

Using Excel 2016 (screenshot below)

Go to Data > New Query > From File > From XML

Using Excel 2013 or Excel 2010

Go to Power Query > From File > From XML

Select the xml file that contains the data.  Click Import.

Select the XML Data Source.  A preview of the xml data will be shown.  If it looks good, press Edit.

 

STEP 2: This will open up the Power Query Editor.  You can now perform your data manipulation here but we will keep the data as is.

Click Close & Load from the Home tab and this will open up a brand new worksheet in your Excel workbook with the imported table.

You now have your new table from the xml file!

 

Frequently Asked Questions

Where can I find the XML import option in Excel?
Go to the Data tab → Get Data → From File → From XML, or use From Web if the file is hosted online.

Can I transform nested XML structures into flat tables?
Yes, Power Query lets you expand nested elements and convert them into a structured, tabular format.

Is Power Query available in all Excel versions?
Power Query is built-in from Excel 2016 onward and available as an add-in in Excel 2010 and 2013.

Can I refresh the XML data after importing?
Absolutely. You can refresh the query to pull in new data from the same XML source.

What if my XML data doesn’t load correctly?
Make sure the XML is well-formed. Power Query may not process malformed XML files; use a validator to check the format.