Table.SplitColumn Function in Power Query M (5 Easy Examples)

If you want to split one column into several columns, by a delimiter or by fixed character positions, Table.SplitColumn is the function for the job.

You give it the column to split and a splitter function, and it hands back the same table with that column broken into new ones.

Table of Contents

Syntax of Table.SplitColumn Function

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

where

table (required, table). The table that holds the column you want to split.
sourceColumn (required, text). The name of the column to split.
splitter (required, function). The splitter function that decides where to cut, for example Splitter.SplitTextByDelimiter or Splitter.SplitTextByPositions.
columnNamesOrNumber (optional, any). Either a list of names for the new columns, or just a number saying how many to create.
default (optional, any). The value used when a row has fewer pieces than there are new columns. Defaults to null.
extraColumns (optional, any). What to do when a row has more pieces than there are columns. Takes an ExtraValues.Type value. Defaults to ExtraValues.Ignore.

Returns: a table, the same one you passed in but with sourceColumn replaced by the new split columns. Short rows are padded with default; extra pieces are handled per extraColumns.

In plain terms, it takes one column, cuts each value into pieces, and lays those pieces out as separate columns.

Example 1: Split a full name into first and last

You have a Full Name column and want first and last name in their own columns.

Here is the starting data:

Full Name
Priya Nair
David Chen
Maria Lopez

Split on the space and name the two new columns:

let
    Source = Excel.CurrentWorkbook(){[Name="Example1"]}[Content],
    Split = Table.SplitColumn(Source,"Full Name",Splitter.SplitTextByDelimiter(" "),{"First","Last"})
in
    Split

The space becomes the cut point, and the Full Name column is gone.

The result has the two new columns:

First	Last
Priya	Nair
David	Chen
Maria	Lopez

Each value is cut at its space, so the first piece lands in First and the second in Last.

Example 2: Split on a multi-character delimiter

A delimiter can be more than one character. Here a comma and a space separate the city from the state.

Here is the starting data:

Location
Austin, TX
Pune, MH
Denver, CO

Pass ", " as the delimiter so the comma is not left clinging to the city:

let
    Source = Excel.CurrentWorkbook(){[Name="Example2"]}[Content],
    Split = Table.SplitColumn(Source,"Location",Splitter.SplitTextByDelimiter(", "),{"City","State"})
in
    Split

The whole ", " string is treated as one delimiter.

The result splits cleanly:

City	State
Austin	TX
Pune	MH
Denver	CO

If you had split on just ",", every state would arrive with a leading space.

Example 3: Fill blanks with a default value

When a row has fewer pieces than columns, the leftover columns get null unless you set default.

Here is the starting data:

Full Name
Arjun Mehta
Sofia
James Park

Sofia has no last name. Pass "N/A" as the default so the empty slot is filled:

let
    Source = Excel.CurrentWorkbook(){[Name="Example3"]}[Content],
    Split = Table.SplitColumn(Source,"Full Name",Splitter.SplitTextByDelimiter(" "),{"First","Last"},"N/A")
in
    Split

The default argument only touches columns that would otherwise be empty.

The result fills the gap:

First	Last
Arjun	Mehta
Sofia	N/A
James	Park

Sofia produces only one piece, so the Last column takes the "N/A" default instead of null.

Example 4: Split by character positions

Sometimes there is no delimiter at all, just fixed-width parts. In that case, split by position. Splitter.SplitTextByPositions cuts at each index you give it.

Here is the starting data:

Product Code
PRD2024A
TPL2025B
KIT2023C

The first three characters are a category, the next four a year, the last one a grade. Cut at positions 0, 3, and 7:

let
    Source = Excel.CurrentWorkbook(){[Name="Example4"]}[Content],
    Split = Table.SplitColumn(Source,"Product Code",Splitter.SplitTextByPositions({0,3,7}),{"Category","Year","Grade"})
in
    Split

Each number is the index where a new piece begins.

The result has three columns:

Category	Year	Grade
PRD	2024	A
TPL	2025	B
KIT	2023	C

Positions are zero-based, so 0 starts the first piece, 3 starts the second, and 7 starts the third.

Example 5: Split on several different delimiters

What if one value uses two different delimiters? Splitter.SplitTextByEachDelimiter walks through a list of them in order.

Here is the starting data:

Tag
2024-Q1:West
2025-Q3:East
2024-Q2:North

A hyphen separates the year from the quarter, and a colon separates the quarter from the region. Pass both delimiters in the order they appear:

let
    Source = Excel.CurrentWorkbook(){[Name="Example5"]}[Content],
    Split = Table.SplitColumn(Source,"Tag",Splitter.SplitTextByEachDelimiter({"-",":"}),{"Year","Quarter","Region"})
in
    Split

The first delimiter makes the first cut, the second makes the next.

The result breaks into three columns:

Year	Quarter	Region
2024	Q1	West
2025	Q3	East
2024	Q2	North

The delimiters are applied left to right, so their order in the list has to match their order in the text.

Things to keep in mind with Table.SplitColumn

The source column is replaced, not kept. After the split, sourceColumn is gone and the new columns sit in its place. Duplicate the column first if you need the original.
Name count drives the column count. Passing {"First","Last"} forces exactly two columns. Pass a number instead of names (for example 2) when you only care about how many, not what they are called.
Extra pieces are dropped by default. With ExtraValues.Ignore (the default), a value with more pieces than columns loses the overflow. Pass ExtraValues.Error to fail loudly, or ExtraValues.List to collect the leftovers into a list in the final column.
default and extraColumns are positional. To set extraColumns you must still pass default before it (use null if you want its default), since they are the 5th and 6th arguments.
New columns come in as text with no type. The split pieces are untyped text. A Year like 2024 stays text until you add a Table.TransformColumnTypes step.
The splitter must be a function, not a string. Passing a bare " " throws Expression.Error: We cannot convert a value of type Text to type Function. Wrap it in Splitter.SplitTextByDelimiter(" ").

Common questions about Table.SplitColumn

What is the difference between Table.SplitColumn and Text.Split?

Text.Split works on a single text value and returns a list. Table.SplitColumn runs across a whole table column and lays the pieces out as new columns, which is what the Split Column button in the editor uses.

Can I split a column into rows instead of columns?

Not with this function. Use Splitter.SplitTextByDelimiter to get a list, then an expand step like Table.ExpandRecordColumn to turn nested values into their own rows.

List of All Power Query Functions

Related Power Query Functions / Articles: