Splitter.SplitTextByRanges Function in Power Query

The Splitter.SplitTextByRanges function in Power Query, returns a function that splits text into a list of text according to the specified ranges (specified offsets and lengths).

Syntax

Splitter.SplitTextByRanges(
    ranges as list, 
    optional startAtEnd as nullable logical
) as function

The function has the following parameters:

ranges: A list of lists, where each inner list contains two numbers: {startPosition, length}.
- startPosition: The zero-based index where the substring starts.
- length: The number of characters to extract starting from startPosition.
startAtEnd: It is an optional parameter. A logical value (true or false). If true, the splitting starts from the end of the text. Default is false (starts from the beginning).

Example: Splitting an 8-digit string column into Branch Code, Subject Code, and Version.

Power Query M

// Start of the 'let' expression to define transformation steps
let
  // Step 1: Create a table named 'Source' using Table.FromRecords
  // Each record contains a single field "Data" with 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 columns using SplitTextByRanges
  // Each range is defined as a pair {start, length}
  //   - {0,2} extracts the first 2 characters (Branch Code)
  //   - {2,4} extracts the next 4 characters (Subject Code)
  //   - {6,2} extracts the final 2 characters (Version)
  return = Table.SplitColumn(
    Source, 
    "Data", 
    Splitter.SplitTextByRanges({{0, 2}, {2, 4}, {6, 2}}), 
    {"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.SplitTextByRanges function in Power Query

Note: The Splitter.SplitTextByRanges, Splitter.SplitTextByLengths, and Splitter.SplitTextByPositions functions all 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