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!  

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

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!

Import Data from XML in Excel