How to Calculate the Running Total by Group Using the Excel Power Query – 2 Methods


The dataset showcases  Items and Sales over three months. To calculate the running total by group (Month), choose a month:



Method 1 – Running Total by Group

Step 1: Opening the Power Query Editor 

  • Select the dataset and go to the Data tab.
  • In Get & Transform Data, choose From Table/Range.

Opening Power Query Editor

  • The following window will be displayed.

Opening Power Query Editor

Note: To avoid repetitive tasks, you can duplicate Table1 (right-click Table1 and choose Duplicate).

Finding Index Number

Step 2: Finding the Index 

  • Go to Add Column tab > Index Column > From 1.

Finding Index Number

A new column displays the index number for each Item.

Finding Index Number

Step 3: Determining Running Totals

  • Click Custom Column in Add Column.
  • In the Custom Column dialog box, rename the New column name as Running Totals
  • Enter the following formula as shown.

=List.Sum(List.Range(#“Added Index”[Sales],0,[Index]))

The List.Sum function sums the values and the List.Range function defines the range (Sales) and changes it depending on the Index value.

Adding Custom Column


This is the output.

Excel Power Query Running Total By Group

Step 4: Applying the Group By Feature

  • Go the Home tab.
  • In Transform, select Group By.
  • In the Group By dialog box, choose Month.
  • In Operation, choose All Rows.

Group By option

  • Click OK.

Data is grouped.

Excel Power Query Running Total By Group

Step 5: Calculating Running Totals by Group

  • To find the running totals by group, open the Advanced Editor in the View tab.

Excel Power Query Running Total By Group

  • Enter the following code.
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}, {"Items", type text}, {"Sales", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Month"}, {{"Count", each _, type table [Month=nullable text, Items=nullable text, Sales=nullable number]}}),

   //Function to Compute Running Totals 

   RunTotalFunction = (RunTotalTable as table) as table =>
    #"Added Index" = Table.AddIndexColumn(RunTotalTable, "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Running Totals", each List.Sum(List.Range(#"Added Index"[Sales],0,[Index])))
    #"Added Custom",

    //Assigning the Function
    RunTotals = Table.TransformColumns(#"Grouped Rows", {"Count", each RunTotalFunction(_)})

Excel Power Query Running Total By Group

The Runfunction was created to compute the running totals and declare RunTable as a table. The Index for the RunTable column and Running Totals was calculated. (you can copy the code from Table1 and enter RunTable instead of #”Changed Type”).

The function was called and #”Grouped Rows” and “Count” for each Runfunction were assigned to determine the running totals.

If you click on the February Table, you’ll get all information including the running total for that month.

Excel Power Query Running Total By Group

Read More: How to Calculate Running Total in One Cell in Excel

Method 2 – Using the List.Accumulate Function

Step1: Computing Running Totals

  • Find the index (as described in Step 2 of the first method) for Table3.

Using the List.Accumulate Function

  • Add a custom column: Running Totals.
  • Enter the following formula.
= List.Accumulate(
            #"Changed Type"[Sales],
      (state, current)=>state + current)

The List.Accumulate function accumulates the sum of values in the list. The list is defined using the List.Range function in the Sales column.

Excel Power Query Running Total By Group Using the List.Accumulate Function

This is the output.

Excel Power Query Running Total By Group Using the List.Accumulate Function

Step 2: Apply the Group By Option

  • Choose Month in Group by.

Using the List.Accumulate Function

You’ll see the running total by group (month). If you pick any Table from the Count table for any Month, you’ll get the running total for that month.

Excel Power Query Running Total By Group Using the List.Accumulate Function

Practice Section 

Download the .xlsx file from the Download Practice Workbook section. Go to Queries & Connections in the Data tab.

Practice Session

Click Table1 and practice.

Practice Session

Download Practice Workbook

Related Articles

<< Go Back to Excel Running Total | Calculate in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Md. Abdul Kader
Md. Abdul Kader

MD. ABDUL KADER is an engineer with a talent for Excel and a passion for VBA programming. To him, programming is like a time-saving wizard, making data manipulation, file handling, and internet interactions a breeze. His skill set extends to SWM, GIS, RS, and SPSS. He holds a B.Sc in Urban & Regional Planning from Chittagong University of Engineering and Technology and has shifted to become a content developer. In this role, he crafts technical content centred around... Read Full Bio

  1. The List.Accumulate method does not appear to be giving the correct running totals by the month.

    • Hello Michael,
      Please specify your problem in detail or send the excel file via [email protected] email address.


      Thank you for your query.
      You have pointed out a good problem. To solve your problem remove the last bracket from the given Custom column formula in the article above.
      And, another thing, choose the All Rows option in the Operation option list instead of Count Rows in the Group By window.
      Grop By Window

      I hope this solves your problem. Stay with ExcelDemy for more Excel tips, tricks, formulas, and solutions.

      Tanjim Reza

  2. Hi there

    Once you have generated the running totals in each of the groups, which are accessed by clicking on the tables created for each group, how do you then combine all of these new tables into one big table so that you can access these running totals?

    • Hello DONNA ATKINS,
      Thank you for your feedback, the answer to your question is provided in the steps below, so follow along.

      Step 1. First, complete steps 1 through 5 from Method 1 >> now, follow the steps shown in the live demonstration.
      steps to expand table in power query

      Step 2. Next, click on Close & Load drop-down >> select Close & Load to option.
      Using Close & Load to option

      Step 3. Lastly, choose the Table or PivotTable option according to your preference >> load this data into a new worksheet.
      Loading table to new worksheet

      Hopefully, this solves your problem. Have a good day.

Leave a reply

Advanced Excel Exercises with Solutions PDF