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:
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