XML Data manipulation with a Power Query

After you download an Asset Tracking XML Data Dump from the All Devices view, you can use a Microsoft Excel Power Query to manipulate and work on the data. For example, the XML Data dump includes hardware and software information but you may need to create a software only report.

We recommend you use a power query, but you can choose other options to manipulate the data, including PivotTables.

Microsoft Excel Power Query is supported by version 2016 and above or M365. If you use versions 2010 and 2013, you can use the following Excel add-in: Microsoft Power Query for Excel

Due to the reduced feature set for Microsoft Excel online, not all these options may be available if you use the online version.

To use a Microsoft Excel Power Query to work with XML data:

1. Use XML and SQL Data Dump to gather XML data for all Clients or for a specific Client, and save the file when prompted
2. In Microsoft Excel, go to Data > Get Data > From File > From XML

   

3. Select the target XML file and click Import
4. Select the table that displays and click Transform Data

   

   The XML contents are imported to Power Query and the Power Query Editor opens.

5. Select the double arrow in the left column to expand the data

   

   The list of data fields displays.

6. Choose Select All Columns and click OK

   

   All the selected columns display.

7. Depending on the data you require, you may need to expand more data columns using the double arrow in those columns the same way as above. For example, if you want to see the device software, you expand the machine.software column.

   If you expand the machine.software column, additional columns display with the software name, version and installed date.

   

   You can also configure Power Query to expand all the columns.

8. When you are finished expanding columns and you have the data you want, select Close & Load

   

   The selected data is imported into Microsoft Excel, and you can continue to work on it from there.