Power QueryExercises

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
    Local

Result

StateHoursFrom
Run21
Stop13
Run34

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Three runs: two hours running, one stopped, three running. Global grouping (the 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
    Expanded

Result

StateHourRun
Run11
Run21
Stop32
Run43

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Expanded columns take the place of the nested column, so Hour sits before Run.

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.Local as 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.
Functions in this lesson:Table.GroupGroupKind.LocalTable.AddIndexColumn

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