Splitter functions are used as arguments in Table.SplitColumn function. Table.SplitColumn function is used to split column vertically into more columns (1 => 2). Here is the syntax and example of this function.

Table.SplitColumn( 
  table
, sourceColumn 
, splitter function 
, optional Names|Number
, optional default
, optional extraValues
) 

Table.SplitColumn( 
  TableWithColumnToSplit
, "ColumnToSplit"
, Splitter.SplitTextByDelimiter(",")
, {"A","B","C","D"}
, null
, ExtraValues.Ignore
)

• table – table where is the column which we are going to split.
• sourceColumn – column that will be splitted.
• splitter function – function that explains how to split the column.
• Names | Number – names of new columns.
• default – value to place in cells which would be empty because we have more columns then values.
• extraColumns – if we have the opposite situation, number of values is bigger than the number of columns, then this argument decide what to do with extra values.

This is how we give names to new columns. If we have too many column names, extra columns will be filled with default value.

We can name columns by setting some number as fourth argument. In that case, names of columns will be generated as name of initial column + index number ( ColumnToSplit + 1,2,3 ). In the example bellow we are using number three, so there are going to be only 3 columns as a result.

If we don't have enough column names for all the columns, those columns will be missing, but only if we use "ExtraValues.Ignore" (1) as the last argument. There are two more possibilities. We can use "ExtraValues.List" (2), so last column will held all surplus values as a list. Last possibility is to raise an error. This will happen when we use "ExtraValues.Error" (3). "ExtraValues.Ignore" is default value.

Third argument is what interests us the most. It contains function that explains how to vertically slice origin column.

• Splitter.SplitByNothing
• Splitter.SplitTextByAnyDelimiter
• Splitter.SplitTextByDelimiter
• Splitter.SplitTextByEachDelimiter
• Splitter.SplitTextByLengths
• Splitter.SplitTextByPositions
• Splitter.SplitTextByRanges
• Splitter.SplitTextByRepeatedLengths
• Splitter.SplitTextByWhiteSpace
• Splitter.SplitTextByCharacterTransition

Splitter.SplitByNothing

"SplitByNothing" will not split origin column. It will only add new columns filled with default values.

Table.SplitColumn( TableWithColumnToSplit, "ColumnToSplit", Splitter.SplitByNothing(),{"A","B","C","D"},"default",ExtraValues.Error )

If names of new columns and default value are not defined (because they are optional), then two things would still change. Name of column would change, and type of column would become "ABC 123".

Splitter.SplitTextByDelimiter

"Splitter.SplitTextByDelimiter" takes one argument. That argument is delimiter by which text will be splitted.

Table.SplitColumn( TableWithColumnToSplit, "ColumnToSplit", Splitter.SplitTextByDelimiter(","), { "A", "B", "C" }, "value" )

Delimiter can have more than one character. Also, we can see what will happen when delimiter is at start of the text.

Table.SplitColumn( TableWithColumnToSplit, "ColumnToSplit", Splitter.SplitTextByDelimiter("A,"), { "A", "B", "C" }, "value" )

"SplitTextByDelimiter" accepts second optional argument. Default value for this argument is QuoteStyle.None. Difference is made when we use QuoteStyle.Csv. In that case all individual quotes will be removed ("), and double quotes will be transformed into individual quotes ("" => "). So, instead "O""A","B" we will have O"A.

Table.SplitColumn( TableWithColumnToSplit, "ColumnToSplit", Splitter.SplitTextByDelimiter( ",", QuoteStyle.Csv), { "A", "B", "C" }, "value" )

Splitter.SplitTextByAnyDelimiter

Anytime we find any of the delimiters from the list in the text, we will split text. In example bellow, anytime we meet coma or semicolon, we will split the text. In table we can, also, see what will happen if there are two delimiters side by side. Two consecutive delimiters will define one empty cell. "SplitTextByAnyDelimiter" also accepts "QuoteStyle" argument.

Table.SplitColumn( TableWithColumnToSplit, "ColumnToSplit", Splitter.SplitTextByAnyDelimiter( { ",", ";"},QuoteStyle.None), { "A", "B", "C", "D" }, "value" )

Splitter.SplitTextByEachDelimiter

"SplitTextByEachDelimiter" is different from "SplitTextByAnyDelimiter" because now, we will split text consecutively in the same order delimiters have in their list. In our example, first we look for coma, then for colon, and then for coma again. So, if we have three delimiters in our list, we can make maximally three splits, and only if all three delimiters exist in text in proper order.

Table.SplitColumn( TableWithColumnToSplit, "ColumnToSplit", Splitter.SplitTextByEachDelimiter( { ",",":", ","},QuoteStyle.None), { "A", "B", "C", "D" }, "value" )

In second row, in table above, we can see that we only made one split. We split on the first coma, but we couldn't split on semicolon because there were no semicolons after first coma.

Splitter.SplitTextByLengths

This time, splitting is not done by delimiters but with consecutive lengths. The last number in the list is three, that is why our "D" column has value "DDD", and not "DDDD" with 4 D's. If this number was 4 (or 5 or 6…) then we would see all four D's.

Table.SplitColumn( TableWithColumnToSplit, "ColumnToSplit", Splitter.SplitTextByLengths( { 2, 3, 2, 3 } ), { "A", "B", "C", "D" }, "default" )

If we had longer list, for example { 2, 3, 2, 3, 8, 4, 2 }, nothing would change. But, if we had smaller list, for example { 2, 3 }, then last two columns would be filled with default values.

Splitter.SplitTextByPositions

Positions are positive numbers and every position can not be smaller than previous position. This positions define places where text would be splitted. In our examples we can see positions 0–11 as "₀A₁A₂B₃B₄B₅C₆C₇D₈D₉D₁₀D₁₁".

Table.SplitColumn( TableWithColumnToSplit, "ColumnToSplit", Splitter.SplitTextByPositions( { 0, 2, 5, 7, 9 } ), { "A", "B", "C", "D" }, "default" )

In first row we have position "9" which limits value in column "D" to only two D's.
In second row, we only have three positions, so the text is splitted to only three columns.
In third row, we have two same consecutive positions. This will create empty cell in column "B".

Splitter.SplitTextByRanges

In Excel, when we use function "=MID( A1; 5; 3)", we first jump to fifth character and then we take that and next two characters. This is how "SplitTextByRanges" work. Function takes argument that looks like { { 0, 2 } ,{ 3, 1 } } . Each pair of values defines position and length, the same way as MID function. In table bellow, we will see how to split text AABBBCCDDDD.

Table.SplitColumn( TableWithColumnToSplit, "ColumnToSplit", Splitter.SplitTextByRanges( { { 0, 2 }, { 2, 3 }, { 4, 4 } } ), { "A", "B", "C", "D" }, "default" )

Each pair defines one substring from initial column. Substrings can overlap. We can see that black "B" in ColumnToSplit belongs both to "B" and "C" column.

Splitter.SplitTextByRepeatedLengths

This function is similar to "SplitTextByLengths", except all lenths are of the same lenght. Here, we want to split text into groups of three characters. We can notice that last column has only two characters.

Table.SplitColumn( TableWithColumnToSplit, "ColumnToSplit", Splitter.SplitTextByRepeatedLengths( 3 ), { "A", "B", "C", "D" }, "default" )

Splitter.SplitTextByWhiteSpace

SplitTextByWhiteSpace is the same as SplitTextByDelimiter when delimiter is space.

Table.SplitColumn( TableWithColumnToSplit, "ColumnToSplit", Splitter.SplitTextByWhitespace(), { "A", "B", "C", "D" }, "default" )

Table.SplitColumn( TableWithColumnToSplit, "ColumnToSplit", Splitter.SplitTextByDelimiter(" "), { "A", "B", "C" }, "value" )

Splitter.SplitTextByCharacterTransition

This function accepts two arguments. Both arguments can be lists of characters. If character from first list stands before some character from the second list, then text will split between those two characters.

Table.SplitColumn( TableWithColumnToSplit, "ColumnToSplit", Splitter.SplitTextByCharacterTransition( { "A", "B" }, { "B", "C" } ), { "A", "B", "C", "D", "E" }, "default" )

In the table above, we can see that we made a split anywhere between A and B, B and B and B and C.

We can also use functions which decide, are two consecutive characters adequate to be split between. In example bellow we have two functions. First function returns true if character is before or on "B", second function decides whether character is after "B". So this logic says that if first character is A or B, and if second character is any other letter, then split text between them (we will observe only upper letters).

Table.SplitColumn( TableWithColumnToSplit, "ColumnToSplit", Splitter.SplitTextByCharacterTransition( each if _ <= "B" then true else false, each if _ > "B" then true else false ), { "A", "B", "C" }, "default" )

In table above, we can see that we made a split anywhere between B and C, A and F, A and G and B and F.

Sample file can be downloaded here:

In Power Query there are no loops. There are no "For each", "For next", "Do Until" blocks. In Power Query we can accomplish the same thing with recursive functions. Let's say that we want to sum elements of a list, from the start, until our sum reach 10 or more. Question is, after how many elements, our goal will be reached. We can see on the chart that our goal will be reached after 4-th element 2 + 0 + 3 + 5 = 10.

List = { 2,0,3,5,0 }

First we have to establish our initial state. We know that our list is { 2,0,3,5,0 } and that initial sum is zero. We also know that first element in the list has index zero. We can write that into query. This query will call recursive function which will supply the final result.

let 
	List = { 2,0,3,5,0 }
	, Sum = 0
	, ElementIndex = 0
	, NoOfElements = RecursiveFunction ( List, Sum, ElementIndex )
in
	NoOfElements

Second, what is our logic? Our logic will go like this:
0) Does our initial state (Sum=0) meet the condition? It doesn't, so we'll go to next step.
1) We'll add one element to sum. We got 2, this doesn't meet the condition so we go to next step.
2) We'll add another element to sum. We got 2 (2+0), this doesn't meet the condition so we go to next step.
3) We'll add another element to sum. We got 5 (2+3), this doesn't meet the condition so we go to next step.
4) We'll add another element to sum. We got 10 (5+5), this does meet the condition so our answer is 4 elements.

Step 0) is already described in our query. Steps 1-4 are pretty similar. They have the same logic, but initial state for each step is different. All we have to do is to wrap this logic into function and then to call that function with different arguments each time.  Our recursive function is bellow. If condition is satisfied, we will return "Sum", otherwise we will call function again, this time with different arguments. That will repeat until condition is met.

( List, Sum, ElementIndex ) => 
let
  SubTotal = if Sum >= 10 then Sum else
    RecursiveFunction( List, Sum + List{ ElementIndex }, ElementIndex + 1)
in
    SubTotal

Every recursive function has the same logic. First we establish initial state and then we repeat this two steps until condition is met:
– Does current state satisfied the condition? If does, then we have final result.
– If it doesn't, we'll change the state and new state will give as arguments for another call of function itself.

Let's do one more complex example. Now we have a bunch of nested lists. Our goal is to sum all scalar values in those lists. Our condition is that we use all scalar values, so we are looking for the sum of 2 + 7 + 8 + 9 + 3 + 5 + 5 + 4 + 4 + 11 = 58.

NestedLists = {   { { 2, 7 }, { 8, 9 } }
                , { 3 }
                , { { 5, 5 }, { 4, 4 } } 
                , 11     
}

First, we will establish initial state. We know that initial sum is zero, and we have our list. This query will call recursive function which will supply final result.

let 
          NestedLists = {  { { 2, 7 }, { 8, 9 }  }
                         , { 3 }
                         , { { 5, 5 }, { 4, 4 } }   	
                         , 11
          }
        , Sum = 0
        , ElementIndex = 0
	, Total = NestedListsRecursiveFunction ( NestedLists, Sum, ElementIndex )
in
	Total

All we need more is a recursive function. This function is complex. Let's try to make it easier to understand by thinking about simplier NestedLists = { 1, { 2 }, 2, { 3 } }. We can present this list by picture:

First we will count how many toothbrushes are in each package. After that is easy to sum the whole list.

Here is the trick. Addition of all toothbrushes in one package is similar to addition of all toothbrushes in the whole list. This mean that we can use same logic to individual package and to whole list. That means we can use recursion. Here is recursion function.

(NestedLists, Sum, ElementIndex) =>
  let
    NewSum = 
      if ElementIndex < List.Count(NestedLists) then
        if Value.Is(NestedLists{ElementIndex}, List.Type) then
          NestedListsRecursiveFunction(
              NestedLists 
            , Sum + NestedListsRecursiveFunction(NestedLists{ElementIndex}, 0, 0) 
            , ElementIndex + 1
          )
        else
          NestedListsRecursiveFunction(
              NestedLists 
            , Sum + NestedLists{ElementIndex} 
            , ElementIndex + 1
          )
      else
        Sum
  in
    NewSum

With purple code we are passing through all elements of a List. If we reach the final element, function will return sum of that List as final result. For each element we are using red code to determin if element is List or not.  If element is scalar, we will use green line above to add such element. Problem is that beside scalar elements, we can also have subList elements.

Orange code is used to add such elements. It is similar to green code. Green code is adding scalar values, and orange code is adding sums of subLists. Blue code above is used to sum every subList. Blue code is using recursion.

So this is our goal, we want to replace every subList with its sum. When we reach subList element, we will dive in it with recursive call to the function. If that subList element has only scalars, green line of code will give us sum of that subList. If that is not true, we will dive deeper until we find subList that contains only scalars. When we get sums of lowest subLists, we can use those to calculate sums for subLists that are higher in hierarchy.

Excel file with sample in Power Query: