Table.ExpandListColumn

What is Table.ExpandListColumn?

Table.ExpandListColumn is a function in the M language that allows users to expand a list column within a table into multiple rows. This is particularly useful when working with data that has a hierarchical structure. For example, if you have a table that contains a column of orders, each order might contain a nested column of products. By using Table.ExpandListColumn, you can expand the list of products within each order into individual rows, making it easier to analyze the data.

The Syntax of Table.ExpandListColumn

The syntax of Table.ExpandListColumn is as follows:

The first argument, “table”, is the table that you want to expand. The second argument, “column”, is the name of the column that contains the list that you want to expand. The function returns a new table with the expanded list.

Examples of Using Table.ExpandListColumn

Let’s take a look at some examples of how to use Table.ExpandListColumn.

Example 1: Expand a Simple List Column

Suppose you have a table called “Orders” that contains a column called “Products”. The “Products” column contains a list of products for each order. To expand the list into individual rows, you can use the following code:

This code creates a new table called “ExpandProducts” that has a row for each product in the “Products” list.

Example 2: Expand a Nested List Column

Suppose you have a table called “Orders” that contains a column called “Customer”. The “Customer” column contains a record with a nested column called “Products”. To expand the “Products” column into individual rows, you can use the following code:

This code first expands the “Customer” column into its nested columns using Table.ExpandRecordColumn. It then expands the “Products” column using Table.ExpandListColumn.

Table.ExpandListColumn is a powerful function in the M language that allows users to expand a list column within a table into multiple rows. This function is particularly useful when working with hierarchical data structures or when trying to analyze data that has been stored in a list format. By understanding the syntax of the function and how to use it in practice, you can unlock the full potential of Power Query and make your data analysis more efficient and effective.

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

Upcoming Courses

Contact Us