Downloads
Power Query MSplitters and combinersBeginner

Splitter.SplitTextByDelimiter

Returns a splitter that cuts text into pieces wherever a chosen delimiter appears, such as a comma, a tab or a semicolon.

Syntax
Splitter.SplitTextByDelimiter(delimiter, optional quoteStyle)

Returns: A function. You pass it to a step such as Table.SplitColumn or List.Transform, and it gives back a list of pieces.

What it does

A splitter is a rule for cutting text. Splitter.SplitTextByDelimiter builds a rule that cuts at one delimiter. Use it to split a column such as "Account,Name,Amount" into separate columns, or a text field where pieces are separated by a semicolon. You pass the splitter to a step that does the cutting, most often Table.SplitColumn.

Syntax and arguments

Example

A bank export puts the payee, the reference and the amount in one column, separated by semicolons. The reviewer wants three columns.

let
    Source = Table.FromRecords({
        [Line = "Acme Supply;INV-4471;1250.00"],
        [Line = "Bolt Hardware;INV-4472;480.00"]
    }),
    Split = Table.SplitColumn(Source, "Line",
        Splitter.SplitTextByDelimiter(";", QuoteStyle.None),
        {"Payee", "Reference", "Amount"})
in
    Split

Split has three columns, and each row holds the payee, the invoice reference and the amount as text. Convert the amount to a number before you total it.

Example: a second, different use

Splitting one text value into a list: Splitter.SplitTextByDelimiter(",")("Q1,Q2,Q3") returns {"Q1", "Q2", "Q3"}. The second pair of brackets calls the splitter on the text.

Things to watch

In business terms

Pulling fields out of a single text column is common with older system exports, memo fields and mainframe reports. A visible split step is easy for a reviewer to test against a few sample rows.

Related functions

All Power Query M functions · View as Markdown · M basics