List.Split

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function List.Split

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 <> ” “)

in

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))

in

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)

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)