Power Query: Making Your Mark(up) Part 3

Welcome to our Power Query blog.  This week, I look at the Xml.Document() function.

Extensible Markup Language (XML) is a markup language which can be read 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 from the Microsoft and is an sample of some accounting data to use with the eConnect system (‘A sample XML document to import Analytical Accounting information on a Payables Transaction when using eConnect’); if you’d like to play along, you may access the data here.  I have created the file locally, and when I open it with a web browser, the data is shown in black text.

As indicated by the message at the top of the previous image, I am shown the document tree.  XML files often have the data nested, which will require some manipulation in Power Query.  I may close some of the nesting to show a single transaction record:

In part 1, I imported the data, and decided that taPMDistribution is the query I am most interested in.  I looked at the steps of the query, beginning with the Source step:

The M code was:

When I looked at the syntax in the Microsoft help pages, I found the basic syntax, but no help with the parameters.

Last time, I entered a step to the taPMDistribution query to see if I could find out any more about the parameters:

I found information for one of my parameters, the optional encoding option has the type Text.Encoding and had a dropdown for the values:

However, the contents (which should not be optional) were shown as optional and the options were not accessible.  The possible values for options remain a mystery!

This time, I will continue looking at ways of importing XML files.  I am going to use the #shared functionality which I last used in the blog Generating the A to Z of M Functions.  

I convert the record ‘Into Table’ and search for “XML”:

The last item is Xml.Tables(), which I have used.  Now, let’s have a look at Xml.Document():

The good news is that I have the syntax and I can that the difference is that this function ‘returns the contents of the XML document as a hierarchical table’.  The bad news is that this also shows the contents incorrectly as optional!  However, there are no options this time:

I take a duplicate copy of taPMDistribution and change the source step:

If I compare this source step with the one from the original query:

I can see that I have different columns this time.  I click in the white space next to the ‘Table’ in the Value column:

This shows me the PMTransactionType record, which contains the queries that I extracted using the XML connector.  I delete the remaining steps, as I will need to recreate the extraction process.  If I extract the data from this table, keeping the original column as prefix, I can see the queries:

I expand the data in the Value.Value column:

Now I can see the structure of the XML file and if I scroll across, I see the table associated with taPMDistribution (though it has the suffix “_items”):

If I click on this ‘Table’, I might expect to navigate to the same query data:

Not quite!  I have more layers to go.  This process explains why the function Xml.Files() was needed to extract XML data as it is much easier to access the ‘flattened’ data.

Next time, I will investigate accessing XML files from the web.

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