Table.AlternateRows
TableKeeps and drops alternating groups of rows based on a repeating keep/drop pattern.
Syntax
Table.AlternateRows(table as table, offset as number, keepNext as number, dropNext as number) as tableParameters
| Name | Type | Required | Description |
|---|---|---|---|
table | table | Yes | The table to process. |
offset | number | Yes | The zero-based index of the first row to start the keep/drop pattern. |
keepNext | number | Yes | The number of consecutive rows to keep in each cycle. |
dropNext | number | Yes | The number of consecutive rows to drop after each keep group. |
Return Value
table — A table containing only the kept rows according to the alternating pattern.
Remarks
Table.AlternateRows applies a repeating keep/drop pattern to the rows of a table. Starting at the zero-based offset row, it keeps keepNext consecutive rows, then discards dropNext consecutive rows, and repeats this pattern until the end of the table. All rows before offset are unconditionally kept.
This function is primarily useful when consuming data that has a fixed repeating structure — for example, raw exports where every second row is a separator, or flat files where every Nth row contains a record header that should be discarded after parsing. For purely row-count-based skipping or selection, Table.Skip and Table.FirstN are simpler alternatives.
Be aware that Table.AlternateRows does not typically fold to the data source, so it will be evaluated locally. For large datasets, consider pre-filtering at the source before applying this function.
Examples
Example 1: Sample every other row (keep 1, drop 1)
let
Source = #table(
{"OrderID", "CustomerName", "Product"},
{
{1, "Alice", "Widget A"},
{2, "Bob", "Gadget B"},
{3, "Charlie", "Widget C"},
{4, "Alice", "Gadget D"},
{5, "Diana", "Widget A"},
{6, "Bob", "Thingamajig E"},
{7, "Charlie", "Gadget B"},
{8, "Diana", "Widget C"}
}
)
in
Table.AlternateRows(Source, 0, 1, 1)OrderID | CustomerName | Product | |
|---|---|---|---|
| 1 | 1 | Alice | Widget A |
| 2 | 3 | Charlie | Widget C |
| 3 | 5 | Diana | Widget A |
| 4 | 7 | Charlie | Gadget B |
Example 2: Keep first 2 rows, drop 1, then repeat
Table.AlternateRows(
#table({"Row"}, {{"A"}, {"B"}, {"SEP"}, {"C"}, {"D"}, {"SEP"}, {"E"}, {"F"}}),
0, 2, 1
)Row | |
|---|---|
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
| 5 | E |
| 6 | F |
Example 3: Use offset to preserve a header before the pattern starts
Table.AlternateRows(
#table({"Row"}, {{"Header"}, {"Data1"}, {"SEP"}, {"Data2"}, {"SEP"}}),
1, 1, 1
)Row | |
|---|---|
| 1 | Header |
| 2 | Data1 |
| 3 | Data2 |