Table.SplitColumn
TableSplits a text column into multiple columns using a splitter function.
Syntax
Table.SplitColumn(table as table, sourceColumn as text, splitter as function, optional columnNamesOrNumber as any, optional default as nullable any, optional extraColumns as nullable any) as tableParameters
| Name | Type | Required | Description |
|---|---|---|---|
table | table | Yes | The table containing the column to split. |
sourceColumn | text | Yes | The name of the column to split. |
splitter | function | Yes | A splitter function such as Splitter.SplitTextByDelimiter or Splitter.SplitTextByLengths. |
columnNamesOrNumber | any | No | Optional. A list of new column names, or a number indicating how many columns to produce. |
default | any | No | Optional. The value to use when a split produces fewer parts than expected columns. |
extraColumns | any | No | Optional. Controls how extra split parts beyond the expected column count are handled. |
Return Value
table — A new table where the specified column has been split into multiple columns.
Remarks
Table.SplitColumn divides the values of a text column into multiple output columns using a splitter function. The source column is replaced by the new columns in the same position. The Splitter.* namespace provides the standard splitter functions: Splitter.SplitTextByDelimiter, Splitter.SplitTextByLengths, Splitter.SplitTextByPositions, and Splitter.SplitTextByCharacterTransition.
The columnNamesOrNumber parameter controls column naming: supply a list of strings for explicit names, or a number to auto-generate names in the form sourceColumn.1, sourceColumn.2, etc. The default parameter fills in values for rows where the split produces fewer parts than expected columns — without a default, missing parts become null. The extraColumns parameter controls what happens when a split produces more parts than column names (e.g., ExtraValues.Ignore to discard extras, ExtraValues.Error to raise an error).
As a PQLint best practice, always supply the default parameter when using Splitter.SplitTextByDelimiter on real-world data, since some rows may not contain the delimiter and will produce fewer parts than expected.
Examples
Example 1: Split FullName in the Employees table into first and last name
let
Employees = #table(
{"EmployeeID","FullName","Department","Salary"},
{{"E001","alice smith","Sales",55000},{"E002","BOB JONES","Engineering",95000},
{"E003","Charlie Brown","Marketing",72000},{"E004","diana PRINCE","Engineering",68000}}
)
in
Table.SplitColumn(
Employees,
"FullName",
Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv),
{"FirstName","LastName"},
null,
ExtraValues.Ignore
)EmployeeID | FirstName | LastName | Department | Salary | |
|---|---|---|---|---|---|
| 1 | E001 | alice | smith | Sales | 55000 |
| 2 | E002 | BOB | JONES | Engineering | 95000 |
| 3 | E003 | Charlie | Brown | Marketing | 72000 |
| 4 | E004 | diana | PRINCE | Engineering | 68000 |
Example 2: Split a fixed-width employee ID code into prefix and number
let
Employees = #table(
{"EmployeeID","FullName"},
{{"E001","alice smith"},{"E002","BOB JONES"},{"E003","Charlie Brown"}}
)
in
Table.SplitColumn(
Employees,
"EmployeeID",
Splitter.SplitTextByLengths({1, 3}),
{"IDPrefix","IDNumber"}
)IDPrefix | IDNumber | FullName | |
|---|---|---|---|
| 1 | E | 001 | alice smith |
| 2 | E | 002 | BOB JONES |
| 3 | E | 003 | Charlie Brown |
Example 3: Split a delimited email field and use a default for rows with no delimiter
let
Customers = #table(
{"Name","Email"},
{{"Alice","alice@example.com"},{"Bob","bob@example.com"},{"Charlie","charlie@example.com"}}
)
in
Table.SplitColumn(
Customers,
"Email",
Splitter.SplitTextByDelimiter("@"),
{"LocalPart","Domain"},
"unknown",
ExtraValues.Ignore
)Name | LocalPart | Domain | |
|---|---|---|---|
| 1 | Alice | alice | example.com |
| 2 | Bob | bob | example.com |
| 3 | Charlie | charlie | example.com |