List.Split

Updated on

List.Split is a Power Query M function that splits a list into a list of lists, where each sublist contains a specified number of elements from the source list. The function returns a list of sublists with the specified size.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

List.Split(
   list as list,
   pageSize as number,
) as list

Description

The List.Split function segments a list into smaller sub-lists based on a specified size. It allows users to divide a larger list into multiple, more manageable chunks, each containing a defined number of elements as specified in the pageSize argument.

Examples

Let’s see a few examples for the List.Split function.

Basic operations

Before diving into real-world applications, it’s essential to grasp the basic logic of the function. Here are some simple operations to demonstrate how List.Split works.

Splitting a list of numbers from 1 to 8 into pairs:

List.Split( { 1..8 }, 2) // Output: { { 1,2 }, { 3,4 }, { 5, 6 }, { 7, 8 } }

Dividing the same list into groups of three:

List.Split( { 1..8 }, 3) // Output: { { 1, 2, 3 }, { 4, 5, 6 }, { 7, 8 } }

Segmenting the list into two groups of four:

List.Split( { 1..8 }, 4) // Output: { { 1, 2, 3, 4 }, { 5, 6, 7, 8 } }

It’s worth noting that the List.Split function isn’t exclusive to numeric lists; it can be applied to any list type.

Splitting Student Groups

Suppose you’re an educator with a list of five students. You want to divide them into project groups, with each group containing two students. Here’s how you can achieve this:

// Output: { { "Student1", "Student2"}, { "Student3", "Student4" }, {"Student5" } }
List.Split( 
  { "Student1", "Student2", "Student3", "Student4", "Student5" }, 
  2
)

Dividing Survey Responses

Imagine you’ve conducted a survey and have gathered 120 responses. To facilitate a more focused analysis, you decide to segment these responses into four equal groups, each containing 30 responses. Here’s how you can utilize the List.Split function to accomplish this:

List.Split( { "Response1", "Response2", ... "Response120" }, 30)

By using this function, you’ll obtain a list of lists, where each inner list contains 30 distinct survey responses. This segmentation can be particularly useful for tasks like comparative analysis, where you might want to compare responses across different segments.

In conclusion, the List.Split function is an invaluable tool for anyone looking to dissect larger datasets into more digestible portions.

Learn more about List.Split in the following articles:

Other functions related to List.Split are:

BI Gorilla Youtube Channel

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/list-split