Power Query: Making Your Mark(up) Part 5

Welcome to our Power Query blog.  This week, I examine the queries created when I extracted an XML file from the web.

Extensible Markup Language (XML) is a markup language understood by multiple platforms and also uses text which can be read.  It is commonly used to store data which is used by more than one platform.  Accounting data may be stored in this way when it is accessed by more than one accounting software system.  The example I am using today comes is on GitHub, and may be accessed here if you wish to follow along.  The data represents DNA data from a crime scene sample!

Last time, I accessed the file from the web, using the ‘From Web’ connector in the ‘Get Data’ dropdown.   I imported two [2] queries:

The catchily-named http://www w3 org/2001/XMLSchema-instance contains information about the original URL.  I am more interested in the DNARecord query:

Let’s look at the M code in the Source step:

The syntax being used begins with the Xml.Tables() function we first encountered in Part 1:

Here, it is nested around the Web.Contents() function.

This automatic behaviour is difficult to avoid.  I choose to create a new ‘Blank Query’ from ‘Other Sources’ on the ‘New Source’ dropdown on the Home tab:

I begin by entering the Web.Contents() part of the nesting:

As soon as I enter this code, it is transformed:

The ‘.xml’ extension triggers inclusion of the Xml.Tables() function.  If I enter the steps separately in the ‘Advanced Editor’, accessed from the Home tab, I may create two [2] separate steps:

Thus, I have the two steps in the ‘Applied Steps’ pane:

I may now view the first Source step:

This way, I may view the XML file.  The Markup step then isolates the Xml.Tables() step:

The individual queries may now be accessed by expanding the Table column:

The first expansion shows me the contents of the http://www w3 org/2001/XMLSchema-instance query, and I must expand Table.Table to get the DNARecord query data:

Accessing the data in this way gives me more information about the source of DNARecord, which may be found in the Name column.

Next time, I will extract the data from the DNARecord query.

Come back next time for more ways to use Power Query!