Splitter.SplitTextByPositions Function in Power Query

The Splitter.SplitTextByPositions function in Power Query's M language splits a text string into a list of text segments based on specified positions. It’s a splitter function commonly used with transformations like Table.SplitColumn to divide text into multiple columns or lists at exact character positions.

Syntax

Splitter.SplitTextByPositions(
    positions as list, 
    optional startAtEnd as nullable logical
) as function

The function has the following parameters:

positions: A list of numbers indicating the character positions where the text should be split.
startAtEnd: It is an optional parameter. A logical value (true or false). If true, splitting starts from the end of the text; if false or omitted, it starts from the beginning.

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 at each position specified in the positions list.
Positions are zero-based (the first character is at position 0).
The resulting segments include the text from the start (or end, if startAtEnd is true) up to each position, plus any remaining text after the last position.
If a position is beyond the text length, it’s ignored.
Consecutive identical positions create empty segments.

Example:

Power Query M

// Start the 'let' expression block for defining the transformation steps
let
  // Step 1: Create a table named 'Source' using Table.FromRecords
  // Each record has one field named "Data" that contains an 8-digit string
  Source = Table.FromRecords(
    {
      [Data = "01569500"], 
      [Data = "02547801"], 
      [Data = "03380707"], 
      [Data = "14745847"], 
      [Data = "75727804"], 
      [Data = "56537809"], 
      [Data = "44568305"], 
      [Data = "08778001"]
    }
  ), 

  // Step 2: Split the "Data" column into three new columns by specifying exact character positions
  // SplitTextByPositions({0, 2, 6}) tells Power Query to:
  //   - Take characters from position 0 to 2 as "Branch Code"
  //   - Take characters from position 2 to 6 as "Subject Code"
  //   - Take characters from position 6 to end as "Version"
  return = Table.SplitColumn(
    Source, 
    "Data", 
    Splitter.SplitTextByPositions({0, 2, 6}), 
    {"Branch Code", "Subject Code", "Version"}
  )

// Final output of the query
in
  return

The output of the above code is shown in the image below:

Splitter.SplitTextByPositions Function in Power Query

Note: The Splitter.SplitTextByRanges, Splitter.SplitTextByLengths and Splitter.SplitTextByPositions functions rely on accurate inputs (ranges, lengths, or positions) to produce the correct output. If these are incorrect, the resulting substrings will be wrong.

Previous Next