Xml.Tables

In this article, we will explore the M code behind the Xml.Tables function and how it can be used to extract and transform data.

What is XML?

XML stands for eXtensible Markup Language. It is a markup language that is used to store and transport data. XML files are structured in a hierarchical format and can be quite complex. Each element in an XML file has a name and contains data or other elements.

Understanding the Xml.Tables Function

The Xml.Tables function is a built-in function in Power Query that allows you to extract data from an XML file and convert it into a table format. This function takes a single argument, which is the URL or file path of the XML file.

Let’s take a look at an example of how the Xml.Tables function works:

In this example, we are using the Xml.Tables function to extract data from an XML file located at https://www.example.com/data.xml. The resulting table will contain three columns, named Column1, Column2, and Column3.

The M Code Behind Xml.Tables

The M code behind the Xml.Tables function is what makes it possible to extract and transform data from an XML file. Let’s take a look at the M code for the example we just used:

The M code is written in a declarative language, meaning that it defines what the data should look like, rather than how to get it. Let’s break down each line of the code:

– The first line, “let”, defines a variable named “Source” that contains the data from the XML file.

– The second line uses the Xml.Tables function to extract the data from the XML file and store it in the “Source” variable.

– The third line uses the Table.ExpandTableColumn function to expand the “Table” column in the “Source” variable and create three new columns named “Column1”, “Column2”, and “Column3”.

– The fourth line renames the new columns to “Column1”, “Column2”, and “Column3” using the curly braces.

Using Xml.Tables to Extract Data

Now that we understand the M code behind the Xml.Tables function, let’s explore how we can use it to extract and transform data from an XML file.

Using the Xml.Tables Function

To use the Xml.Tables function, you simply need to provide it with the URL or file path of the XML file you want to extract data from. For example:

This code will extract the data from the XML file located at https://www.example.com/data.xml and store it in the “Source” variable.

Transforming Data with Xml.Tables

Once you have extracted the data from the XML file using the Xml.Tables function, you can use various other functions in Power Query to transform and manipulate the data.

For example, let’s say that the XML file contains a date in the format “YYYYMMDD”. You can use the Text.Start and Text.End functions to extract the year, month, and day from the date and create three new columns:

This code will extract the year, month, and day from the “Date” column in the “Source” variable and create three new columns named “Year”, “Month”, and “Day”.

The Xml.Tables function is a powerful tool in Power Query that allows you to extract and transform data from XML files. By understanding the M code behind this function, you can gain a deeper understanding of how it works and how to use it to extract and transform data. With the right combination of functions, you can easily manipulate even the most complex XML files and extract the data you need.

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

Upcoming Courses

Contact Us