<!-- https://learnsql.cpetoday.com/functions/m/table-splitcolumn -->
# Table.SplitColumn

Language: Power Query M
Category: Table: columns
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
Summary: Splits one column into several columns, by a delimiter, by position or by a splitter you define.
Returns: The table with the split columns in place of the original column.
Level: Intermediate
Related: Table.CombineColumns, Table.ExpandListColumn, Table.TransformColumns, Table.PromoteHeaders

## 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
- **table**: the table to change. Required.
- **sourceColumn**: the name of the column to split, in quotes. Required.
- **splitter**: how to split. Use `Splitter.SplitTextByDelimiter("-")` for a character, or `Splitter.SplitTextByPositions({0, 4})` for fixed widths. Required.
- **columnNamesOrNumberOfColumns**: a list of names for the new columns, such as `{"Account", "Department", "Class"}`. Strongly recommended.
- **default**: a value for pieces that are missing. Optional.
- **extraColumns**: what to do with extra pieces. Optional.

## 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.

```powerquery
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.

```powerquery
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
- Name exactly as many columns as the widest value splits into. Rows with extra pieces can cause errors, depending on the options you set.
- Splitting produces text. Convert the pieces to numbers or dates afterwards.
- Check the result for stray spaces and blanks, which the split leaves behind.
- Query folding: splitting usually does not fold, so check View Native Query and place the step with that in mind.

## 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.
