Power Query Problems

How to Create a Power Query Running Total by Group in Excel

Huma Ashraf Ch Oct 21, 2026 868 views

Question details

The user needs to calculate a running total within specific groups without having the calculated values carry over from earlier groups.

How to Create a Power Query Running Total by Group

Product: Excel Power Query
Device & OS: not provided
Scenario: Grouping data by FileID and calculating a cumulative sum of hours for each group within the Power Query editor.
Observed behavior: Using List.FirstN on the full previous-step column incorrectly sums values across different FileID groups instead of resetting the total for each specific group.

Before you start

Ensure your dataset is loaded into the Power Query Editor and correctly formatted as a table before attempting to modify the M code.

Solution 1Recommended

Calculate Grouped Running Total Using M Code

Group data by category, apply a zero-based index to each group, and use List.Sum with List.FirstN to calculate the running total securely within each boundary.

By default, calculating a running total over an entire column in Power Query ignores group boundaries. To isolate the calculation, you must nest the data by group, assign a sequential index, and limit the sum strictly to the current group's subset of rows.

Sort Data by Group and Order

Select your FileID column and sort it ascending. Then, select your chronological column (such as Date or Time) and sort it ascending to ensure the running total flows in the proper sequence.

Group Rows by FileID

Navigate to the Transform tab and select "Group By". Choose the FileID column and select "All Rows" to nest the data into sub-tables.

Add an Index Within Groups

Add a custom column to apply a zero-based index to each nested sub-table using the Table.AddIndexColumn function so that each group has its own independent numbering.

Apply the Running Total Formula

While working within the grouped tables, calculate the running total with the expression List.Sum(List.FirstN([Hours], [Index] + 1)). Referencing the localized [Hours] column ensures earlier FileID groups are ignored.

Expand the Final Table

Click the expand icon on the customized nested column to reveal the original columns alongside your newly calculated running total.

Calculate Grouped Running Total Using M Code

Index Numbering Requirement: The index must strictly start at 0 so that the [Index] + 1 formula accurately captures the correct number of rows for List.FirstN in the current operational step.

Calculate Grouped Running Totals Easily in WPS Spreadsheets

Instead of dealing with complex Power Query M code, you can easily calculate a running total by group in WPS Spreadsheets using standard Excel-compatible formulas like SUMIFS. It is highly compatible, fast, and completely bypasses advanced coding requirements.

1. Open your spreadsheet in WPS Office: Launch WPS Spreadsheets and open the file containing your categorized data, such as your FileID and Hours.
2. Sort the dataset: Highlight your data range, navigate to the Data tab, and sort by the FileID column first, followed by your date or sequence column.
3. Apply the SUMIFS function: In a new column next to the first row of data (e.g., row 2), enter =SUMIFS($B$2:B2, $A$2:A2, A2) assuming Column A is FileID and Column B is Hours.
4. Fill the formula down: Drag the fill handle down to the bottom of your dataset. The formula will automatically compute an accurate running total that properly resets for each FileID group.

Fully compatible with Microsoft Excel (.xlsx) formatsSupports standard SUMIFS functions for easy grouped running totalsFree, lightweight, and fast alternative for data analysisFamiliar user interface with zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Power Query running total duplicate or carry over values?

This happens if the calculation references the entire previous step's column instead of the localized sub-table for the current group. Nesting the data into groups and using a localized reference ensures the calculation does not carry over.

Do I need to sort my data before creating a running total in Power Query?

Yes. A running total fundamentally relies on the sequential order of rows. You must sort your table logically within each group before adding an index and applying the calculation.

Can I calculate a grouped running total without using Power Query?

Yes. You can use a standard spreadsheet formula like SUMIFS with expanding ranges (locking the first cell reference but leaving the second relative) to calculate a running total by category directly in the normal worksheet view.

Excel & Data Problems