Table.SplitAt
TableSplits a table into two parts at a given row count, returning both as fields of a record.
Syntax
Table.SplitAt(table as table, count as number) as recordParameters
| Name | Type | Required | Description |
|---|---|---|---|
table | table | Yes | The table to split. |
count | number | Yes | The number of rows in the first portion. |
Return Value
record — A record with two fields: First (the first count rows) and Remaining (all other rows).
Remarks
Table.SplitAt divides a table into exactly two disjoint sub-tables at the row boundary specified by count. The result is a record with two fields:
- First: the first count rows.
- Remaining: all rows from position count onward.
This is semantically equivalent to calling Table.FirstN(table, count) and Table.Skip(table, count) separately, but expresses the two-part split intent as a single named operation. Use Split[First] and Split[Remaining] to access each part.
Table.SplitAt is useful when you want to process the head and tail of a table differently — for example, treating the first N rows as a header block and the remainder as data, or separating a "seed" batch from the remaining rows in an incremental load pattern. For splitting into more than two parts, use Table.Split with a page size.
Examples
Example 1: Split the Sales table into first 3 rows and the rest
let
Sales = #table(
{"OrderID","CustomerName","Product","OrderDate"},
{{1,"Alice","Widget A",#date(2024,1,15)},{2,"Bob","Gadget B",#date(2024,1,18)},
{3,"Charlie","Widget C",#date(2024,2,1)},{4,"Alice","Gadget D",#date(2024,2,10)},
{5,"Diana","Widget A",#date(2024,3,5)}}
)
in
Table.SplitAt(Sales, 3)Result | |
|---|---|
| 1 | [First = [Table], Remaining = [Table]] |
Example 2: Access and count rows in both parts
let
Sales = #table(
{"OrderID","CustomerName"},
{{1,"Alice"},{2,"Bob"},{3,"Charlie"},{4,"Alice"},{5,"Diana"},{6,"Bob"},{7,"Charlie"},{8,"Diana"}}
),
Split = Table.SplitAt(Sales, 5),
FirstSize = Table.RowCount(Split[First]),
RestSize = Table.RowCount(Split[Remaining])
in
[FirstSize = FirstSize, RestSize = RestSize]Result | |
|---|---|
| 1 | [FirstSize = 5, RestSize = 3] |
Example 3: Process the head and tail separately, then recombine
let
Orders = #table({"OrderID","CustomerName"},{{1,"Alice"},{2,"Bob"},{3,"Charlie"},{4,"Diana"}}),
Split = Table.SplitAt(Orders, 2),
Head = Table.AddColumn(Split[First], "Priority", each "High", type text),
Tail = Table.AddColumn(Split[Remaining], "Priority", each "Standard", type text)
in
Table.Combine({Head, Tail})OrderID | CustomerName | Priority | |
|---|---|---|---|
| 1 | 1 | Alice | High |
| 2 | 2 | Bob | High |
| 3 | 3 | Charlie | Standard |
| 4 | 4 | Diana | Standard |