How to Calculate Running Total by Group Using Excel Power Query

Running totals simply means the cumulative sum of a sequence of values. The application of calculating running total is quite significant for business purposes e.g. operating cash register, balancing transactions, and so on. However, you may easily compute the running total, particularly for a group of data using the Power Query, a data transformation and preparation engine in Excel. In this article, I’ll demonstrate you to how to calculate the running total by group using the Power Query feature in Excel with proper explanation.

Excel Power Query Running Total by Group: 2 Methods to Calculate

Let’s introduce today’s dataset where Sales of some Items are provided based on three months. Now, you need to calculate the running total by group (e.g. Month). That means if you choose a particular month, you’ll get the running total for that month only.


Now, let’s dive into the methods.

1. Running Total by Group

Initially, you’ll see the calculation of the running total first, then the application of the Group By option, and lastly, the running total by the group. Let’s enjoy the process in a step-by-step process.

Step 01: Opening the Power Query Editor 

Obviously, you need to open the Power Query Editor window to use the Power Query feature in Excel. Please do the below steps.

➤ Before opening the window, you need to select the dataset first and then choose the From Table/Range option from the Get & Transformed Data tab in the Data tab.

Opening Power Query Editor

➤ Immediately, you’ll get the following window having the dataset.

Opening Power Query Editor

Note: To avoid the repetitive task, you may duplicate Table1 (right-click over Table1 and choose the Duplicate option).

Finding Index Number

Step 02: Finding the Index 

If you want to measure the running total, you have to find the index. For finding it, go to Add Column tab > Index Column dropdown > From 1 option.

Finding Index Number

Then, you’ll get a new column having the index number for each Item.

Finding Index Number

Step 03: Determining Running Totals

At this stage, you have to determine the running total (not by group). For this, click on the Custom Column option from the Add Column tab.

Then, you’ll see the Custom Column dialog box. Next, rename the New column name as Running Totals and insert the following formula in the specified place as shown in the below screenshot.

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

Here, List.Sum function provides the sum of the values within it and List.Range function defines the range of Sales and it will change depending on the Index value.

Adding Custom Column

Note: While inserting the formula for each time, make sure that No syntax errors have been detected is appeared.

So, the output will look as follows.

Excel Power Query Running Total By Group

Step 04: Applying the Group By Feature

Now, apply the Group By option from the Home tab in the Transform ribbon. In the Group By dialog box, choose the Month from the drop-down list and All Rows from the drop-down list of the Operation.

Group By option

After pressing OK, you’ll get the following grouped data.

Excel Power Query Running Total By Group

Step 05: Calculating Running Totals by Group

In the previous step, you didn’t find any running totals in the grouped data. To find the running totals by group, you need to open the Advanced Editor from the View tab.

Excel Power Query Running Total By Group

Insert the following code in the Advanced Editor dialog box.

    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

Here, I created a function namely Runfunction to compute the running totals along with the declaration of the RunTable as a table. Then, I calculated the Index for the RunTable column and Running Totals (Also, you can copy the code from Table1 and type the RunTable instead of #”Changed Type”).

Subsequently, you need to call the function and assign the #”Grouped Rows” and “Count” for each Runfunction to determine the running totals.

However, you’ll get the final output. If you click on the Table of February month, 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

2. Alternative Way: Using the List.Accumulate Function

Alternatively, you may calculate the running total by a group in Excel using the List.Accumulate function.

Step 01: Computing Running Totals First

Firstly, you need to find the index (discussed in Step 2 of the first method) for Table3.

Using the List.Accumulate Function

Then, add a custom column namely Running Totals, and insert the following formula.

= List.Accumulate(
            #"Changed Type"[Sales],
      (state, current)=>state + current)

Here, List.Accumulate function accumulates the sum of values from the list. And the list is defined using the List.Range function of the Sales column.

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

Eventually, you’ll get the following output.

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

Step 02: Apply the Group By Option

Now, you need to apply the Group By option. Just choose the Month from the drop-down list.

Using the List.Accumulate Function

Shortly, you’ll get the following running total by group (i.e. month). If you pick any Table from the Count table of any Month, you’ll get the running total for that particular month.

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

Practice Section 

If you want to practice with the used dataset, please download the .xlsx file from the Download Practice Workbook section. Then, go to Queries & Connections from the Data tab.

Practice Session

And click on Table1 on the right-side window of your download practice workbook.

Practice Session

Download Practice Workbook


That’s the end of today’s session. From the beginning to the end, I discussed the 2 ways to calculate the running total by group using the Power Query engine in Excel. So, I strongly believe this article will be highly beneficial for you. Anyway, if you have any queries or recommendations, please share them in the comments section below.

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