Table.SplitColumn

Understanding the Table.SplitColumn Function

The Table.SplitColumn function in Power Query is used to split a column in a table into multiple columns. This function takes in three arguments: the table to be split, the name of the column to be split, and the split options.

When applying the Table.SplitColumn function, Power Query creates a new table with additional columns based on the split options. The original column is removed from the table, and the new columns are added to the right of the original table.

The M Code Behind Table.SplitColumn

Behind the scenes, the M code that powers the Table.SplitColumn function is relatively straightforward. When calling the function, Power Query generates the following M code:

Let’s go through each of these arguments in more detail.

Source

The first argument, Source, refers to the table to be split. This argument should be a reference to the table or a step that generates the table.

ColumnName

The second argument, ColumnName, refers to the name of the column to be split. This argument should be a string that matches the exact name of the column.

Splitter

The third argument, Splitter, refers to the delimiter used to split the column. This argument should be a text value that matches the exact delimiter used in the column. If there is no delimiter, you can use null.

SplitterPattern

The last argument, SplitterPattern, refers to the pattern to be used when splitting the column. This argument should be a string that matches the pattern in the column. If there is no pattern, you can use null.

Examples of Using Table.SplitColumn

Now that we understand the M code behind Table.SplitColumn let’s go through some examples of how we can use it to manipulate our data.

Example 1: Splitting a Column by Delimiter

Suppose we have a table with a column of full names, and we want to split the column into two columns, one for first names and one for last names. We can use the Table.SplitColumn function to achieve this as follows:

In this example, we are splitting the column called “FullName” by the space delimiter. We then create two new columns called “FirstName” and “LastName.”

Example 2: Splitting a Column by Pattern

Suppose we have a table with a column of email addresses, and we want to split the column into two columns, one for the username and one for the domain. We can use the Table.SplitColumn function to achieve this as follows:

In this example, we are splitting the column called “Email” by the “@” symbol. We then create two new columns called “Username” and “Domain.”

The Table.SplitColumn function in Power Query is a powerful tool that enables you to split columns in your data to create new columns. By understanding the M code behind this function, you can use it to manipulate your data effectively. With the examples provided in this article, you should be able to start using Table.SplitColumn to split columns in your data easily.

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

Upcoming Courses

Contact Us