Power Query: Making Your Mark(up) Part 1
15 January 2025
Welcome to our Power Query blog. This week, I look at importing Extensible Markup Language (XML) files.
Extensible Markup Language (XML) is a markup language which can be followed by multiple platforms and also uses text which may also 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 selected manipulation in Power Query. I may close some of the nesting to show a single transaction record:
To begin the extraction process to Excel, I am going to start in a new Excel workbook. On the Data tab I find the ‘From XML’ option in the ‘From File’ tab of the ‘Get Data’ dropdown:
I navigate to my file, and choose to Import it:
Note that since the records are nested within the XML file, it appears as a folder, and I may open the groupings to select the data I wish to transform. I also check the ‘Select multiple items’ box:
I have opted to select all the tables available to me. I choose to ‘Transform Data’:
I have three [3] queries. The first, taAnalyticsDistribution, appears to hold information about which ledgers in the eConnect database should be updated and the distribution. The next query, taPMDistribution, holds information about the amounts and document types and is of more interest to me.
Finally, taPMTransactionInsert contains information about the batch that the transaction in linked to:
Having decided that taPMDistribution is the query I am most interested in, let’s look at the steps of the query, beginning with the Source step:
The M code is:
= Xml.Tables(File.Contents("C:\Users\kathr\OneDrive\Documents\SUMPRODUCT\PQ Blog\xml sample file.xml"))
Since this is the first time I’ve looked at the Xml.Tables() function, let’s have a look at the syntax.
Xml.Tables(contents as any, optional options as nullable record, optional encoding as nullable number) as table
Microsoft tells me that this function “returns the contents of the XML document as a nested collection of flattened tables”. However, there is no information about the options or the encoding parameters!
That’s it for this week, I’ll recover from my disappointment and continue looking at XML tables and the M code I may use to manipulate them next time.
Come back next time for more ways to use Power Query!
