Xml.Document

The Xml.Document function in Power Query is a powerful tool that can be used to extract data from XML documents. It takes an XML document as input and returns a table that represents the data in the XML document. In this article, we will take a closer look at the M code behind the Xml.Document function and how it can be used to extract data from XML documents.

Understanding the Xml.Document Function

The Xml.Document function in Power Query takes an XML document as input and returns a table that represents the data in the XML document. The input to the function can be in the form of a file path, a URL or a text string. The function parses the XML document and returns a table with columns that correspond to the elements in the XML document.

The table returned by the Xml.Document function can be further transformed using other Power Query functions such as Table.SelectColumns, Table.RenameColumns, and Table.TransformColumns. This makes it possible to extract only the required data from the XML document and transform it into a format that is suitable for analysis.

The M Code Behind Xml.Document

The M code behind the Xml.Document function is a series of steps that are executed by Power Query to extract data from the XML document. The M code is generated automatically by Power Query when the Xml.Document function is used.

Here is an example of the M code that is generated by Power Query when the Xml.Document function is used:

The M code starts with the ‘let’ keyword which is used to define a variable called ‘Source’. The ‘Source’ variable contains the result of calling the Xml.Document function with the file path to the XML document as input.

The next line of the M code uses the ‘Record.ToTable’ function to convert the ‘Source’ variable into a table. This table contains columns that correspond to the elements in the XML document.

Extracting Data from the Xml.Document Output

Once the Xml.Document function has been executed and the output has been converted to a table, we can use other Power Query functions to extract the required data from the table.

For example, let’s say we have an XML document that contains information about customers. We can use the Xml.Document function to extract the data from the XML document and then use the Table.SelectColumns function to select only the columns that we are interested in.

Here is an example of the M code that can be used to extract the customer name and email address from an XML document:

The M code starts with the ‘let’ keyword which is used to define a variable called ‘Source’. The ‘Source’ variable contains the result of calling the Xml.Document function with the file path to the customers.xml file as input.

The next line of the M code uses the ‘Record.ToTable’ function to convert the ‘Source’ variable into a table. This table contains columns that correspond to the elements in the XML document.

The last line of the M code uses the ‘Table.SelectColumns’ function to select only the ‘Name’ and ‘EmailAddress’ columns from the table.

The Xml.Document function in Power Query is a powerful tool that can be used to extract data from XML documents. The M code behind the function is generated automatically by Power Query and can be further transformed using other Power Query functions. This makes it possible to extract only the required data from the XML document and transform it into a format that is suitable for analysis. By understanding the M code behind the Xml.Document function, users can take full advantage of the power of Power Query to perform complex data transformations with ease.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us