Table.Split

D

Back to top

T

Back to top

#

Back to top

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

What is the Table.Split function?

The Table.Split function is used to split a table column into multiple columns based on a delimiter. The syntax for the Table.Split function is as follows:

- The 'table' argument is the table that contains the column you want to split.

- The 'column' argument is the name of the column you want to split.

- The 'separator' argument is the delimiter that separates the values in the column.

- The 'newColumnNames' argument is a list of new column names you want to give to the split columns. If this argument is not specified, Power Query will automatically name the columns as Column1, Column2, and so on.

How does the Table.Split function work?

The Table.Split function works by splitting a column into multiple columns based on a delimiter. Let's take an example to understand how it works. Consider the following table:

| Name | Age | Profession |

|---------------|-----|---------------|

| John, Doe | 35 | Engineer |

| Jane, Smith | 28 | Accountant |

| Michael, Wong | 43 | Data Analyst |

Suppose we want to split the 'Name' column into two separate columns, 'First Name' and 'Last Name', based on the comma delimiter. We can use the Table.Split function to achieve this. The M code for this operation would be:

– The first argument ‘#”Previous Step”‘ represents the previous step in the Power Query Editor.

– The second argument “Name” is the name of the column we want to split.

– The third argument “,” is the delimiter used to split the values in the column.

– The fourth argument {“First Name”, “Last Name”} is the list of new column names we want to assign to the split columns.

When we execute this M code, we get the following table:

| First Name | Last Name | Age | Profession |

|————|———–|—–|————–|

| John | Doe | 35 | Engineer |

| Jane | Smith | 28 | Accountant |

| Michael | Wong | 43 | Data Analyst |

As we can see, the ‘Name’ column has been split into two columns, ‘First Name’ and ‘Last Name’, based on the comma delimiter.

Additional options for the Table.Split function

The Table.Split function has some additional options that can be used to customize the behavior of the function. Let’s take a look at these options.

Count

The Count option is used to specify the number of columns to split the column into. If the Count option is specified, Power Query will split the column into the specified number of columns, even if there are extra values in the column. For example, consider the following table:

| ID | Values |

|—-|—————-|

| 1 | A,B,C,D,E,F,G,H |

Suppose we want to split the ‘Values’ column into three separate columns, each containing three values. We can use the Table.Split function with the Count option to achieve this. The M code for this operation would be:

- The first argument '#"Previous Step"' represents the previous step in the Power Query Editor.

- The second argument "Values" is the name of the column we want to split.

- The third argument 3 is the number of columns we want to split the 'Values' column into.

When we execute this M code, we get the following table:

| ID | Column1 | Column2 | Column3 |

|----|---------|---------|---------|

| 1 | A | B | C |

| 1 | D | E | F |

| 1 | G | H | |

As we can see, the 'Values' column has been split into three columns, each containing three values.

Else

The Else option is used to specify what to do with extra values in the column that cannot be split into the specified number of columns. If the Else option is not specified, Power Query will return an error if there are extra values in the column. For example, consider the following table:

| ID | Values |

|----|-------------|

| 1 | A,B,C,D,E,F |

Suppose we want to split the 'Values' column into three separate columns, each containing two values. We can use the Table.Split function with the Count and Else options to achieve this. The M code for this operation would be:

– The first argument ‘#”Previous Step”‘ represents the previous step in the Power Query Editor.

– The second argument “Values” is the name of the column we want to split.

– The third argument 2 is the number of columns we want to split the ‘Values’ column into.

– The fourth argument {“Column1”, “Column2”, “Column3”} is the list of new column names we want to assign to the split columns.

– The Else option is set to Split.ErrorHandling.Ignore, which means that any extra values in the ‘Values’ column will be ignored.

When we execute this M code, we get the following table:

| ID | Column1 | Column2 | Column3 |

|—-|———|———|———|

| 1 | A | B | |

| 1 | C | D | |

| 1 | E | F | |

As we can see, the ‘Values’ column has been split into three columns, each containing two values. The extra value at the end of the column has been ignored.

The Table.Split function is a powerful M function in Power Query that allows you to split a table column into multiple columns based on a delimiter. By understanding the M code behind this function, you can customize its behavior and extract specific values from your data. With the additional options available for this function, you can handle extra values in the column and split the column into a specified number of columns. Overall, the Table.Split function is a useful tool for data transformation and manipulation in Power Query.

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)