Group consecutive rows with GroupKind.Local
Summarise runs of consecutive values instead of all occurrences of a value.
~9 min read · advanced · Module 13: Group by and aggregation
By Tahir Nas · Examples run in our simulator · Updated
Before this: Group by with several aggregations, Remove blank rows and keep an explicit row order
A machine log records the state every hour: Run, Run, Stop, Run. 'How long was each stoppage?' isn't a question about all Stop rows together; it's about each run of consecutive identical states. Ordinary grouping merges all Run rows into one group. Local grouping starts a new group whenever the key changes.
let
Log = #table({"Hour", "State"}, {{1, "Run"}, {2, "Run"}, {3, "Stop"}, {4, "Run"}, {5, "Run"}, {6, "Run"}}),
Global = Table.Group(Log, {"State"}, {{"Hours", each Table.RowCount(_), Int64.Type}}),
Local = Table.Group(Log, {"State"}, {{"Hours", each Table.RowCount(_), Int64.Type}, {"From", each List.Min([Hour]), Int64.Type}}, GroupKind.Local)
in
LocalResult
| State | Hours | From |
|---|---|---|
| Run | 2 | 1 |
| Stop | 1 | 3 |
| Run | 3 | 4 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Global step) would give just Run 5 and Stop 1.Order is the whole point
Local grouping depends on row order, so make the order explicit first: sort by the timestamp or sequence number, as the blank rows lesson recommends. On an unsorted table, the runs mean nothing.
Typical uses
- Stoppages, outages or status streaks in time-ordered logs.
- Blocks in a report export where each block starts with a different label (an alternative to fill down when the label repeats on every line).
- Sessions: consecutive events by the same user.
- Compressing a long sequence into ranges: 'pages 3–7 were scanned in colour'.
Numbering the runs
Add an index after the local group to give each run an ID, then expand an All Rows column if you need the detail back with its run number:
let
Log = #table({"Hour", "State"}, {{1, "Run"}, {2, "Run"}, {3, "Stop"}, {4, "Run"}}),
Runs = Table.Group(Log, {"State"}, {{"Rows", each _, type table}}, GroupKind.Local),
Numbered = Table.AddIndexColumn(Runs, "Run", 1, 1, Int64.Type),
Expanded = Table.ExpandTableColumn(Numbered, "Rows", {"Hour"})
in
ExpandedResult
| State | Hour | Run |
|---|---|---|
| Run | 1 | 1 |
| Run | 2 | 1 |
| Stop | 3 | 2 |
| Run | 4 | 3 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Where to find this in Excel
The Group By dialog has no option for local grouping. Add GroupKind.Local to the step's formula in the formula bar or in the Advanced Editor.
Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Grouping or summarizing rows (Microsoft Learn); Use Power Query to transform data (the Power Query editor) (Microsoft Learn).
Common mistakes
- Using global grouping for streak or run questions and merging separate runs.
- Applying local grouping to rows that aren't sorted by time or sequence.
- Forgetting that dirty values ("Run " vs "Run") break a run into pieces.
Excel, Power BI and the simulator
- The Group By dialog in Excel and Power BI Desktop doesn't offer local grouping; add
GroupKind.Localas the fourth argument in the formula bar. - Local grouping is evaluated in memory and won't fold to a database source.
- The simulator supports GroupKind.Local and GroupKind.Global.
Next step
Check that the ideas in this lesson stuck. The quiz explains every answer, including the wrong ones.
Take the Group by and aggregation quiz