Table.SplitAt

Table.SplitAt is a Power Query M function that returns a list containing two tables: one with the first N rows of the input table (as specified by count) and another with the remaining rows. The function returns a list of two tables.

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

Syntax

Table.SplitAt(
   table as table,
   count as number,
) as list

Description

Table.SplitAt returns a list containing the first count rows specified and the remaining rows. It provides two tables: one with first N rows of table (as per count) and another with the remaining rows.

Examples

Let’s see how the Table.Split function can work in practice. Let’s say you have a table of 6 rows, and you want to split it into two. You want the first table to have 4 rows, and the remainder to be in the second table.

The way to do that is using the following code:

Table.SplitAt( Source, 4 )

In the below image, the first table object stores the first 4 rows and all other rows end up in the second table:

However, you can easily change this composition. In case you only want a single row in the first table, and all the remainder in the second, you would use:

Table.SplitAt( Source, 1 )

The result then looks like the below image:

Try it yourself

To try this yourself you can use:

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" }
      }
    ),
    FourRowsInFirstTable = Table.SplitAt( Source, 4 ),
    OneRowInFirstTable = Table.SplitAt( Source, 1 )
in
    OneRowInFirstTable

Related functions

Other functions related to Table.SplitAt are:

• Table.Partition
• Table.PartitionValues
• Table.Split
• Table.ToColumns
• Table.ToList
• Table.ToRecords
• Table.ToRows
• Table.Transpose

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