Splitter.SplitTextByDelimiter Function in Power Query

The Splitter.SplitTextByDelimiter function in Power Query's M language creates a splitter function that divides a text string into a list of text segments based on a specified delimiter. It is commonly used in transformations like Table.SplitColumn to split text data into multiple columns or lists.

Syntax

Splitter.SplitTextByDelimiter(
    delimiter as text, 
    optional quoteStyle as nullable number, 
    optional csvStyle as nullable number
) as function

The function has the following parameters:

delimiter: The text string used to split the input text (e.g., "," for commas).
quoteStyle: It is an optional parameter. It determines how quoted text is handled. Options:
- QuoteStyle.None (0): Treats quotes as regular characters; delimiters within quotes are considered.
- QuoteStyle.Csv (1): Ignores delimiters within quoted sections (default for CSV-like data).
csvStyle (optional): Specifies CSV parsing behavior (less common). Typically used internally or for advanced scenarios.

The function returns a function that can be applied to a text value to perform the split.

How It Works

The function splits the input text wherever the delimiter appears, unless the delimiter is within a quoted section (when QuoteStyle.Csv is used).
The result is a list of text segments.
If the delimiter is not found, the entire text is returned as a single-item list.
Empty segments are created for consecutive delimiters (e.g., ",," produces an empty string between delimiters).

Example: Basic Splitting. Split the text "a,b,c,d" by commas without considering quotes.

Power Query M

let
    Source = Splitter.SplitTextByDelimiter(",", QuoteStyle.None)("a,b,c,d")
in
    Source

The output of the above code is {"a", "b", "c", "d"}. The text is split at each comma.

Example: Split the input by comma, handling quoted text. Split the text "a,""b,c"",d" by commas, respecting CSV-style quotes.

Power Query M

let
    Source = Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv)("a,""b,c"",d")
in
    Source

The output of the above code is {"a", "b,c", "d"}.

How it works: Here we have used QuoteStyle.Csv.

The comma within the quoted section "b,c" is ignored, treating "b,c" as a single segment.
The result splits only at unquoted commas.

Example: Consecutive Delimiters. Split the text "a,,b,c" by commas.

Power Query M

let
    Source = Splitter.SplitTextByDelimiter(",", QuoteStyle.None)("a,,b,c")
in
    Source

The output of the above code is {"a", "", "b", "c"}. Consecutive commas result in an empty string "" between "a" and "b".

Example: Using with Table.SplitColumn. Suppose we have a table with a column "Data" containing a string, and we want to split it into multiple columns by delimiter hyphen.

Power Query M

let
  Source = Table.FromRecords(
    {
      [Data = "1-Ashish-568"], 
      [Data = "2-Katrina-855"], 
      [Data = "3-Alia-380"], 
      [Data = "4-Vicky-458"], 
      [Data = "5-Mohini-278"], 
      [Data = "6-Meenakshi-289"], 
      [Data = "7-Esha-875"], 
      [Data = "8-Anjali-380"]
    }
  ), 
  return = Table.SplitColumn(Source, "Data", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"CustomerID", "Name", "Marks"})
in
    return

The text is split at hyphen (-), creating three columns.

Splitter.SplitTextByDelimiter Function in Power Query

Practical Use Cases

CSV Parsing: Split comma-separated values while respecting quoted sections (e.g., addresses or names containing commas).
Log Files: Break apart log entries separated by delimiters like semicolons or tabs.
Data Cleaning: Split concatenated fields (e.g., "Name|Age|City") into separate columns.
Text Processing: Extract segments from structured text, such as configuration strings.

Previous Next