Table.Split - Table Function

Table.Split is a Power Query M function that splits a table into a list of tables based on a pageSize, where each table contains pageSize rows from the source table. The function returns a list of tables.

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

Syntax

Table.Split(
   table as table,
   pageSize as number,
) as list

Description

Table.Split splits the specified table into a list of tables using the specified page size. It divides table into a list where each table contains a set of pageSize rows from the source table.

Examples

To understand how Table.Split functions, let’s go through an example.

A Basic Example

The Table.Split function takes a table as input, and breaks it up in a number of tables. The number of tables is determined by the pageSize in the function’s second argument.

Suppose you have a table and you want to split it into smaller tables, each containing just 2 rows. You can achieve this with the following code:

Table.Split( Source, 2 )

Here, Source represents your original table. If this table has 6 rows, the function will produce a list containing three tables, each with 2 rows as shown below:

Table.Split breaks down a table into multiple tables in Power Query M

Handling Different Table Sizes

What happens if the total number of rows doesn’t evenly divide by the pageSize? For instance, let’s say you want to split the same table into groups of 4 rows:

Table.Split( Source, 4 )

Table.Split breaks down a table into tables of different sizes in Power Query M

Since the table has 6 rows, the function will create two tables: the first table will have 4 rows, and the second table will contain the remaining 2 rows. The Table.Split function is smart enough to return any leftover rows in a separate table, ensuring that no data is lost during the split.

Try it out yourself

To try this out yourself, you can use the following code:

let
  Source = 
    #table(
      type table[ProductKey = Text.Type, Product = Text.Type, OrderDate = Date.Type, Sales = Text.Type ],
      {
          { "1", "Product A", #date(2024, 1, 15) , "150" },
          { "2", "Product B", #date(2024, 2, 20) , "200" },
          { "3", "Product C", #date(2024, 3, 10) , "175" },
          { "4", "Product D", #date(2024, 4, 25) , "220" },
          { "5", "Product E", #date(2024, 5, 5 ) , "185" },
          { "6", "Product F", #date(2024, 6, 15) , "240" }
      }
    ),
    TwoRowsPerTable = Table.Split( Source, 2 ),
    FourRowsPerTable = Table.Split( Source, 4 )
in
    FourRowsPerTable

In this example, TwoRowsPerTable will create three tables, each with 2 rows. Meanwhile, FourRowsPerTable will create one table with 4 rows and another with the remaining 2 rows.

Other functions related to Table.Split are:

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