Table.SplitColumn
Splits one column into several, with a splitter such as one for a delimiter or for positions.
Group: Table. Power Query M has it too: a query that uses it is written as in Power BI.
Syntax
Table.SplitColumn(table as table, sourceColumn as text, splitter as function, optional columnNamesOrNumber as any, optional default as any, optional extraColumns as any) as table| Parameter | Type | Description |
|---|---|---|
table | table | The table with the column to split. |
sourceColumn | text | The column to split. |
splitter | function | A splitter that turns a value into a list of parts, such as Splitter.SplitTextByDelimiter("-"). |
columnNamesOrNumber | any optional | The new columns' names, or how many there are (named after the column: Sku.1, Sku.2 ...). |
default | any optional | The value for the new columns a cell has no part for; null when left out. |
extraColumns | any optional | What to do with parts beyond the new columns: ExtraValues.Ignore, ExtraValues.List or ExtraValues.Error. |
Returns
table. The new columns where the source column was; the other columns as they were.
Remarks
- Parts beyond the new columns are dropped without an error by default. Transform data writes the column names it saw when you split, so data that later has more parts loses them: give more columns, or split into rows instead.
- A cell with fewer parts gets the default (null) in the columns left over; a null cell gives null in every new column.
Examples
A code in two parts
Table.SplitColumn(#table({"Order", "Sku"}, {{1, "CH-0042"}, {2, "DK-0117"}}), "Sku", Splitter.SplitTextByDelimiter("-"), {"Category", "Number"})| Order | Category | Number |
|---|---|---|
| 1 | CH | 0042 |
| 2 | DK | 0117 |
By positions
Table.SplitColumn(#table({"Sku"}, {{"CH0042"}, {"DK0117"}}), "Sku", Splitter.SplitTextByPositions({0, 2}), {"Category", "Number"})| Category | Number |
|---|---|
| CH | 0042 |
| DK | 0117 |
Fewer and more parts than columns
Table.SplitColumn(#table({"Sku"}, {{"CH-0042"}, {"DK"}, {"LP-0007-B"}}), "Sku", Splitter.SplitTextByDelimiter("-"), {"Category", "Number"})| Category | Number |
|---|---|
| CH | 0042 |
| DK | null |
| LP | 0007 |
DK has no number, so it gets null; the B of the last code is dropped.
Related functions
Splitter.SplitTextByDelimiter, Splitter.SplitTextByPositions, Table.CombineColumns, Table.ExpandListColumn.