Splitter.SplitTextByLengths Function in Power Query

The Splitter.SplitTextByLengths function in Power Query (M language) is used to split a text value into multiple parts based on a list of lengths. It is very useful when you have fixed-width text data and want to extract segments of specified lengths.

Syntax

Splitter.SplitTextByLengths(
    lengths as list,
    optional startAtEnd as nullable logical
) as function

The function has the following parameters:

lengths: A list of numbers specifying the length of each segment to extract.
Example: {3, 2, 4} → first 3 characters, next 2 characters, next 4 characters.
startAtEnd: It is an optional parameter. Logical value (true or false).
- false: Splitting starts from the beginning of the text. It is the default value.
- true: Splitting starts from the end of the text.

Return Value: Returns a function that can be applied to a text value to produce a list of text segments according to the specified lengths.

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

Power Query M

// Start the let expression block to define the query steps
let
  // Step 1: Create a table named 'Source' from a list of records
  // Each record contains a single field called "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 new columns based on fixed lengths
  // The lengths {2,4,2} mean:
  //   - First 2 digits will become "Branch Code"
  //   - Next 4 digits will become "Subject Code"
  //   - Last 2 digits will become "Version"
  return = Table.SplitColumn(
    Source, 
    "Data", 
    Splitter.SplitTextByLengths({2, 4, 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.SplitTextByLengths 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