Table.AlternateRows
Keeps the first rows, then skips and keeps rows in turn, a set number of each, to the end of the table.
Group: Table. Power Query M has it too: a query that uses it is written as in Power BI.
Syntax
Table.AlternateRows(table as table, offset as number, skip as number, take as number) as table| Parameter | Type | Description |
|---|---|---|
table | table | The rows to thin out. |
offset | number | How many rows at the top are kept before the pattern starts. |
skip | number | How many rows to leave out in each turn. |
take | number | How many rows to keep in each turn. |
Returns
table. The rows kept, in their order.
Remarks
- Useful for exports where every second row is a subtotal or a blank line.
Examples
Leave out every second row
let
Export = #table({"Product", "Units"}, {{"Chair", 4}, {"Subtotal", 4}, {"Desk", 1}, {"Subtotal", 5}})
in
Table.AlternateRows(Export, 1, 1, 1)| Product | Units |
|---|---|
| Chair | 4 |
| Desk | 1 |
The first row is kept, the second skipped, the third kept, the fourth skipped.
Keep only the subtotals
let
Export = #table({"Product", "Units"}, {{"Chair", 4}, {"Subtotal", 4}, {"Desk", 1}, {"Subtotal", 5}})
in
Table.AlternateRows(Export, 0, 1, 1)| Product | Units |
|---|---|
| Subtotal | 4 |
| Subtotal | 5 |
Related functions
Table.Skip, Table.RemoveRows, Table.SelectRows, List.Alternate.