A

B

C

D

E

F

G

H

I

J

L

#

Understanding the List.Split Function

The List.Split function has two parameters: the list to be split and the delimiter (or splitting function). The function returns a list of lists, where each sublist contains the elements between the delimiters.

Here is the basic syntax for the List.Split function:

List.Split(list as list, delimiter as any) as list

 The `list` parameter is the input list to be split, and the `delimiter` parameter is either a value or a function that specifies the splitting criteria. 
 If `delimiter` is a value, it is used as the delimiter to split the list. For example, if we wanted to split a list of names by the comma delimiter, we would use the following code:

List.Split({“John, Smith”, “Jane, Doe”, “Bob, Johnson”}, “,”)

 This would return the following list of lists:

{

{“John”, ” Smith”},

{“Jane”, ” Doe”},

{“Bob”, ” Johnson”}

}

 If `delimiter` is a function, it must return a boolean value and is applied to each element in the list. The list is split wherever the function returns `true`. For example, if we wanted to split a list of numbers into sublists of increasing values, we would use the following code:

List.Split({1, 2, 4, 5, 7, 8}, (x, y) => x + 1 <> y)

 This would return the following list of lists:

{

{1, 2},

{4, 5},

{7, 8}

}

 Advanced Examples
 The List.Split function can be used in a variety of ways to manipulate data. Here are some advanced examples:
 Splitting Text into Sentences
 Suppose we have a long text string that we want to split into individual sentences. We can use the List.Split function with a custom splitting function that checks for periods, exclamation marks, and question marks:

let

text = “This is a sample text. It contains multiple sentences! Can you split it?”,

sentences = List.Split(Text.ToList(text), (c1, c2) => List.Contains({“.”, “!”, “?”}, c2) and c1 <> ” “)

sentences

 This would return the following list of sentences:

{

“This is a sample text.”,

“It contains multiple sentences!”,

“Can you split it?”

}

 Splitting Data into Bins
 Suppose we have a list of numbers that we want to split into bins of equal size. We can use the List.Split function with a custom splitting function that checks for the bin boundaries:

let

numbers = {1, 2, 3, 4, 5, 6, 7, 8, 9, 10},

binsize = 3,

bins = List.Split(numbers, (i) => Number.IntegerDivide(i – 1, binsize))

bins

 This would return the following list of bins:

{

{1, 2, 3},

{4, 5, 6},

{7, 8, 9},

{10}

}

 The List.Split function is a powerful tool for manipulating lists in Power Query M. By understanding the M code behind the function and its various parameters, users can split lists in a variety of ways and create customized data transformations.
 Power Query and M Training Courses by G Com Solutions (0800 998 9248)
   	Power BI Training for Facility Management Professionals (2 Days)
 	£1,990.00 Add to basketBuy Now
 
 
 Upcoming Courses
 
 Contact Us
  
        
 Subject
     
 Your Name (required)
      
 Company/Organisation
      
 Email (required)
      
 Telephone
     
 Training Course(s)
 
 Your Message
      
 Upload Example Document(s) (Zip multiple files)