Data manipulation with Power Query

After you download an Data Dump from the Asset Tracking dashboard, you can use an Excel Power Query to transform and work with the data. For example, the XML file includes hardware and software details, but you may want to generate a report that includes only software information.

We recommend using Power Query, but you can also use other Excel features such as PivotTables to manipulate the data.

Some features may not be available in Microsoft Excel Online due to its reduced functionality.

Use Power Query to work with XML data

1. Download the XML file

Use Export a hardware and software data dump to gather data for all clients or for a specific client. Save the file when prompted.

2. Import the XML file into Excel

In Excel, go to Data > Get Data > From File > From XML.

3. Select and transform the data

• Select the XML file and choose Import.
• When the table displays, select Transform Data.
• The Power Query Editor opens and imports the XML contents.

4. Expand the data columns

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

  

• In the list of fields, choose Select All Columns and select OK.
• All selected columns display in the editor.

5. Expand nested columns as needed

• For example, to view device software details, expand the machine.software column.
• This reveals additional columns such as software name, version, and installation date.

  

• You can also configure Power Query to expand all columns automatically.

6. Load the data into Excel

When you're finished expanding columns and have the data you need, select Close & Load. The selected data is imported into Excel, and you can continue working with it.