Downloads
Power Query MTable: columnsIntermediate

Table.SplitColumn

Splits one column into several columns, by a delimiter, by position or by a splitter you define.

Syntax
Table.SplitColumn(table as table, sourceColumn as text, splitter as function, optional columnNamesOrNumberOfColumns as any, optional default as any, optional extraColumns as any) as table

Returns: The table with the split columns in place of the original column.

What it does

Table.SplitColumn breaks one text column into pieces. An account code such as "1000-200-30" can become three columns, and a name written as "Smith, Ann" can become a last name and a first name. Each piece becomes its own column, and the original column is removed.

Syntax and arguments

Example

Account codes in a chart of accounts combine an account, a department and a class, separated by hyphens. The code is split into three named columns.

let
    Source = Excel.CurrentWorkbook(){[Name = "Codes"]}[Content],
    Split = Table.SplitColumn(Source, "Code", Splitter.SplitTextByDelimiter("-"), {"Account", "Department", "Class"})
in
    Split

Each code is cut at its hyphens. The three pieces become Account, Department and Class. Set the types after this step if the pieces should be numbers.

Example: a second, different use

A payroll export gives names as "Smith, Ann". The name is split at the comma and the space is trimmed off the first name.

let
    Source = Excel.CurrentWorkbook(){[Name = "Payroll"]}[Content],
    Names = Table.SplitColumn(Source, "Employee", Splitter.SplitTextByDelimiter(","), {"Last Name", "First Name"}),
    Trimmed = Table.TransformColumns(Names, {{"First Name", Text.Trim, type text}})
in
    Trimmed

Things to watch

In business terms

Splitting codes and names is routine cleaning for ledger and payroll exports. Doing it in the query means the same split happens every month, and the reviewer can see the rule.

Related functions

All Power Query M functions · View as Markdown · M basics