Pinterest Pixel

Import Data from XML Using Power Query or Get & Transform

Bryan
Power Query or Get & Transform (In Excel 2016) lets you perform a series of steps to transform your Excel data.
Import Data from XML Using Power Query or Get & Transform | MyExcelOnline Import Data from XML Using Power Query or Get & Transform | MyExcelOnline

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:

Import Data from XML Using Power Query or Get & Transform

Import Data from XML Using Power Query or Get & Transform | MyExcelOnline

Download excel workbook and source fileGet-Data-from-XML.zip

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

Import Data from XML Using Power Query or Get & Transform

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

Import Data from XML Using Power Query or Get & Transform

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

Import Data from XML Using Power Query or Get & Transform

 

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.

Import Data from XML Using Power Query or Get & Transform

You now have your new table from the xml file!

Import Data from XML Using Power Query or Get & Transform

Import Data from XML in Excel

 

 

728x90

 

If you like this Excel tip, please share it
Import Data from XML Using Power Query or Get & Transform | MyExcelOnline
Bryan

Bryan Hong is an IT Software Developer for more than 10 years and has the following certifications: Microsoft Certified Professional Developer (MCPD): Web Developer, Microsoft Certified Technology Specialist (MCTS): Windows Applications, Microsoft Certified Systems Engineer (MCSE) and Microsoft Certified Systems Administrator (MCSA).

He is also an Amazon #1 bestselling author of 4 Microsoft Excel books and a teacher of Microsoft Excel & Office at the MyExecelOnline Academy Online Course.

See also  Using Excel Tables in Power Pivot

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...