Table.SplitColumn
Table function · By Tahir Nas · Examples run in our simulator · Published · Updated
Splits a column into several using a splitter function.
When to use it
Use it to break a combined field (full name, code-with-suffix, date-time text) into separate columns. Name the output columns explicitly so the step doesn't depend on how many parts the first rows had.
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 tableParameters
- splitter
- For example Splitter.SplitTextByDelimiter(" ").
- columnNamesOrNumber
- Names of the new columns, or how many to create.
Returns
The table with the column replaced by its parts.
Example
Table.SplitColumn(#table({"Name"}, {{"Ana Silva"}}), "Name", Splitter.SplitTextByDelimiter(" "), {"First", "Last"})Result
| First | Last |
|---|---|
| Ana | Silva |
Example: split at the last delimiter only
Splitter.SplitTextByEachDelimiter with startAtEnd = true splits at the last dot only, so names that contain dots stay whole.
Table.SplitColumn(#table({"File"}, {{"sales.2026.csv"}}), "File", Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.None, true), {"Name", "Ext"})Result
| Name | Ext |
|---|---|
| sales.2026 | csv |
Both results are produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M, and checked on every build.
Good to know
- Missing parts become null; extra parts are dropped.
Common mistakes
- Letting Power Query infer the number of columns from the sample, which drops parts later.
- Splitting data that contains the delimiter inside values.
The matching command in Excel
- Excel for Windows
- Home > Split Column > By Delimiter
- What you see
- The column is replaced by parts with a .1, .2 suffix on the original name.
Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Split columns by delimiter (Microsoft Learn).
Exercises that use Table.SplitColumn
- beginner~6 minPro
Split customer names into first and last name (kite shop)
Split Customer at the space into First Name and Last Name columns.
- advanced~15 minPro
Reshape month-and-measure columns into a tidy table (kiosks)
Columns look like "2026-01 Sales" and "2026-01 Units". Produce one row per item and month with Sales and Units columns.
Learn more
- Split columns and extract text by delimiter lesson
- Split column recipes: names, codes, addresses and lists guide
Related functions
Official documentation: Table.SplitColumn on Microsoft Learn.