Table.ExpandTableColumn

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Table.ExpandTableColumn

The Basics of Table.ExpandTableColumn

The `Table.ExpandTableColumn` function takes two arguments: the first is the table to expand, and the second is the name of the column to expand. Let’s take a look at an example:

let

Source = Table.FromRecords({

[Name="John", Age=30, Address=[Street="123 Main St", City="Anytown", State="CA"]],

[Name="Jane", Age=25, Address=[Street="456 Oak Ave", City="Smalltown", State="TX"]]

}),

ExpandAddress = Table.ExpandTableColumn(Source, "Address")

in

ExpandAddress

In this example, we have a table with three columns: Name, Age, and Address. The Address column contains a nested record with three fields: Street, City, and State. We use the `Table.ExpandTableColumn` function to expand the Address column into three separate columns, resulting in a table with six columns.

Handling Lists and Records

Table.ExpandTableColumn is not limited to expanding columns containing nested records. It can also be used to expand columns containing lists or records. Let’s take a look at an example with a list:

let

Source = Table.FromRecords({

[Name="John", Age=30, Skills={"Programming", "Data Analysis", "Project Management"}],

[Name="Jane", Age=25, Skills={"Marketing", "Graphic Design"}]

}),

ExpandSkills = Table.ExpandTableColumn(Source, "Skills")

in

ExpandSkills

In this example, we have a table with three columns: Name, Age, and Skills. The Skills column contains a nested list of skills. We use the `Table.ExpandTableColumn` function to expand the Skills column into multiple columns, resulting in a table with four columns.

Table.ExpandTableColumn can also handle columns containing nested records. Let’s take a look at an example:

let

Source = Table.FromRecords({

[Name="John", Age=30, Contact=[Phone=[Mobile="555-555-1212", Home="555-555-2323"], Email="john@example.com"]],

[Name="Jane", Age=25, Contact=[Phone=[Mobile="555-555-3434"], Email="jane@example.com"]]

}),

ExpandContact = Table.ExpandTableColumn(Source, "Contact")

in

ExpandContact

In this example, we have a table with three columns: Name, Age, and Contact. The Contact column contains a nested record with two fields: Phone and Email. The Phone field contains a nested record with two fields: Mobile and Home. We use the `Table.ExpandTableColumn` function to expand the Contact column into multiple columns, resulting in a table with six columns.

Customizing the Expanded Column Names

By default, Table.ExpandTableColumn will use the names of the nested columns as the new column names when expanding the table. However, you can customize these names by using the optional third argument of the function.

let

Source = Table.FromRecords({

[Name="John", Age=30, Address=[Street="123 Main St", City="Anytown", State="CA"]],

[Name="Jane", Age=25, Address=[Street="456 Oak Ave", City="Smalltown", State="TX"]]

}),

ExpandAddress = Table.ExpandTableColumn(Source, "Address", {"Street", "City", "State"})

in

ExpandAddress

In this example, we use the third argument of the `Table.ExpandTableColumn` function to customize the column names when expanding the Address column.

The `Table.ExpandTableColumn` function is a powerful tool in Power Query that allows you to work with nested data in a flexible and dynamic way. By understanding the M code behind this function and its various use cases, you can take your data analysis to the next level and save time and effort in the process.

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

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)