If you want to drop rows from a table at a fixed interval, like stripping out a separator row that repeats every few rows or thinning a table down to every other row, the Table.AlternateRows function is what you reach for. It works by position, keeping and removing rows on a repeating pattern you define. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.
Syntax of Table.AlternateRows Function
Table.AlternateRows(table as table, offset as number, skip as number, take as number) as table
where
table(required, table). The source table you want to thin out.offset(required, number). How many rows to keep at the top before the repeating pattern starts.skip(required, number). How many rows to remove in each pass of the cycle.take(required, number). How many rows to keep in each pass of the cycle.
Returns: a new table with rows removed on a repeating pattern. Column order and the order of the surviving rows are left unchanged.
In plain terms, it keeps the first offset rows, then walks down the rest of the table dropping skip rows and keeping take rows, over and over.
Example 1: Keep the first row, then every other row
The pattern runs in two phases. First it keeps the offset rows from the top. Then it repeats a cycle: skip skip rows, then keep take rows.
So Table.AlternateRows(Source,1,1,1) keeps the 1st row, then skips one and keeps one, all the way down.
Here is the starting data:
| Day | Reading |
|---|---|
| Mon | 10 |
| Tue | 20 |
| Wed | 30 |
| Thu | 40 |
| Fri | 50 |
| Sat | 60 |
| Sun | 70 |
Run the pattern with an offset of 1, skipping 1 and taking 1:
let
Source = Excel.CurrentWorkbook(){[Name="Readings"]}[Content],
Result = Table.AlternateRows(Source,1,1,1)
in
Result
It keeps Mon from the offset, then skips Tue and keeps Wed, skips Thu and keeps Fri, skips Sat and keeps Sun.
The result keeps every other row, starting from the first:
| Day | Reading |
|---|---|
| Mon | 10 |
| Wed | 30 |
| Fri | 50 |
| Sun | 70 |
The rows you keep are the take rows. The rows you drop are the skip rows.
Example 2: Keep every other row, starting from the second
Set offset to 0 and the cycle starts straight away, with no rows held back at the top. Because each pass skips before it takes, the very first row gets dropped.
This is the easiest place to trip up, so compare it against Example 1, which used the same skip and take but kept the first row.
Here is the starting data:
| Day | Reading |
|---|---|
| Mon | 10 |
| Tue | 20 |
| Wed | 30 |
| Thu | 40 |
| Fri | 50 |
| Sat | 60 |
| Sun | 70 |
Run it with an offset of 0:
let
Source = Excel.CurrentWorkbook(){[Name="Readings"]}[Content],
Result = Table.AlternateRows(Source,0,1,1)
in
Result
With no offset, it skips Mon and keeps Tue, skips Wed and keeps Thu, skips Fri and keeps Sat, then skips Sun.
The result keeps every other row, this time starting from the second:
| Day | Reading |
|---|---|
| Tue | 20 |
| Thu | 40 |
| Sat | 60 |
So to keep the first row and every other one after it, use offset=1, not offset=0.
Example 3: Keep two rows, then skip one
The cycle is not limited to single rows. Here offset is 2, skip is 1, and take is 2, so you keep the first two rows, then repeat a pattern of dropping one and keeping two.
Here is the starting data:
| Day | Reading |
|---|---|
| Mon | 10 |
| Tue | 20 |
| Wed | 30 |
| Thu | 40 |
| Fri | 50 |
| Sat | 60 |
| Sun | 70 |
Keep two at the top, then skip one and take two on each pass:
let
Source = Excel.CurrentWorkbook(){[Name="Readings"]}[Content],
Result = Table.AlternateRows(Source,2,1,2)
in
Result
It keeps Mon and Tue from the offset, skips Wed, keeps Thu and Fri, skips Sat, then keeps Sun.
The result drops only the rows that landed on a skip:
| Day | Reading |
|---|---|
| Mon | 10 |
| Tue | 20 |
| Thu | 40 |
| Fri | 50 |
| Sun | 70 |
Wed and Sat are gone because they were the skipped rows in each pass.
Example 4: Remove a separator row that repeats
This is where Table.AlternateRows really earns its keep. Pasted or exported reports often have a junk row, like a subtotal or a divider, sitting at a fixed interval.
Here a --- subtotal --- row sits after every data row, and you want it gone.
Here is the starting data:
| Item | Value |
|---|---|
| Apples | 120 |
| — subtotal — | 0 |
| Bananas | 95 |
| — subtotal — | 0 |
| Cherries | 60 |
| — subtotal — | 0 |
Keep the first row, then skip one and keep one to peel off each separator:
let
Source = Excel.CurrentWorkbook(){[Name="Export"]}[Content],
Result = Table.AlternateRows(Source,1,1,1)
in
Result
It keeps Apples from the offset, skips the separator and keeps Bananas, skips the next separator and keeps Cherries, then skips the trailing separator.
The result is the clean list, separators removed:
| Item | Value |
|---|---|
| Apples | 120 |
| Bananas | 95 |
| Cherries | 60 |
Because the pattern is position-based, this only works when the junk row sits at a steady interval. If it appears at random spots, filter on its value with Table.SelectRows instead.
Example 5: Drop a repeating two-row block
The block you remove can be more than one row. Flat text exports often repeat a page header and page footer between the real data rows.
Here a page header and page footer pair sits before each data row, so you want to skip two rows and keep one on each pass.
Here is the starting data:
| A | B |
|---|---|
| page header | x |
| page footer | y |
| Data 1 | 11 |
| page header | x |
| page footer | y |
| Data 2 | 22 |
| page header | x |
| page footer | y |
| Data 3 | 33 |
Use an offset of 0, skip 2, take 1:
let
Source = Excel.CurrentWorkbook(){[Name="Log"]}[Content],
Result = Table.AlternateRows(Source,0,2,1)
in
Result
With no offset, each pass skips the header and footer, then keeps the data row that follows.
The result is just the data rows:
| A | B |
|---|---|
| Data 1 | 11 |
| Data 2 | 22 |
| Data 3 | 33 |
The two-row junk block is stripped out on every cycle, leaving only the rows you wanted.
Things to keep in mind with Table.AlternateRows
- The cycle order is skip first, then take. Each pass removes
skiprows before it keepstakerows. Getting this backwards is the usual reason the wrong rows survive. offsetonly runs once. The firstoffsetrows are kept untouched at the top, and the repeating cycle never reaches back to them.- It is position-based, not value-based. It cannot test what is in a row, only where the row sits. For condition-based removal use
Table.SelectRows, and to drop duplicate rows useTable.Distinct. - A partial final pass keeps whatever is left. If fewer than
takerows remain at the end, it keeps them all. If a pass starts with fewer thanskiprows left, those rows are simply dropped. - The interval has to be steady. If the junk row drifts to an irregular position, the pattern shifts and starts cutting real data. Verify against the source before trusting the output.
Common questions about Table.AlternateRows
How is Table.AlternateRows different from Table.SelectRows?
Table.AlternateRows removes rows by their position on a repeating pattern, while Table.SelectRows removes rows by testing a condition on their values. Use the first to thin a table at a fixed interval, the second to filter on what the rows actually contain.
How do I keep every other row instead of removing alternate rows?
That is exactly what it does, depending on offset. Use Table.AlternateRows(Source,1,1,1) to keep the 1st, 3rd, 5th rows, or Table.AlternateRows(Source,0,1,1) to keep the 2nd, 4th, 6th rows.
Is there a column version of this?
Not with the same pattern logic, but to drop or keep columns by name you use Table.SelectColumns. Table.AlternateRows only ever removes rows, never columns.
List of All Power Query Functions
Related Power Query Functions / Articles:
