• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Grouped Running Totals in Power Query

You are here: Home / Power Query / Grouped Running Totals in Power Query
grouped running totals in power query
October 7, 2020 by Philip Treacy

In a previous post I looked at creating running totals in Power Query, here I'll be creating grouped running totals.

I'll start with a table of data showing values for cities within different countries.

source data for grouped running total

Download the Workbook With Sample Query

The queries in this Excel file can be copied/pasted into the Power BI Desktop Advanced Editor and will work there too.

Enter your email address below to download the workbook with the data and code from this post.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook.

I want to create a running total for each country so that we end up with this where the running total resets when we reach a new country in our list.

Example of grouped running total

Grouped Running Total Custom Function

The M code to create the custom function is very similar to the code for the running totals custom function.

Read More : Power Query Custom Functions

You don't need to worry about getting your hands dirty with M, you can just call the function as you would any other function in Power Query.

The function is named fxGroupedRunningTotal and takes two parameters named values and grouping, both of these are lists of equal length. The function returns a list which will have the same number of items as the input lists.

In this case the values parameter will be the list of values from our source table, and grouping will be the list of countries.

Power Query M Code for grouped running total custom function

Here's how the function works. It uses List.Generate to work through each item in the grouping list, following the rules as described below. It uses two variables GRT which holds the running total, and i which is a counter

Each time a new value is created in GRT, it is added to the list that is the result of the function, GRTList

  1. Set the initial values. GRT takes the first value in the values list. The counter i is 0.
  2. Keep looping while i is less than the number of items in the values list. Remember: lists are indexed from 0.
  3. On each loop : if the current country in the list specified by grouping{[i]} is the same as the next country grouping{[i] +1} then add values{[i] + 1} to GRT else the next grouping (country) is different, so GRT just becomes values{[i] + 1}
  4. Add the value in GRT to GRTList

The try .. otherwise in step 3 is to catch the error generated when [i] + 1 exceeds the number of items in the input lists.

Using the Custom Function in a Query

After loading the source table, the query creates two buffered lists for the values and countries. Buffering the lists allows the query to run much faster as the lists are held in memory and not re-evaluated every time the query needs to refer to an item in the lists.

If you want to group by something else in your data, then change Source[Country] to whatever column you want to use.

Then it's just a case of calling the function to create the grouped running total, creating a table from the columns in the source table and the output from the function.

Power Query Using Grouped Running Total Custom Function

The final table loaded into Excel looks like this

Grouped running total calculated in table

How Fast Is This?

As with the running total query, by using buffered lists the query runs in a flash. Even when it's calculating for 100,000 rows, the query finishes in a couple of seconds.

In the example workbook you can download, I've created a table with 100,000 rows of data and a separate query to calculate the grouped running total for that. Try it out for yourself.

Pro Tip - Quickly Create 100,000 Rows of Data

If you need to enter a lot of data, like the 100,000 rows of dummy data for this post, you can do this using the Immediate Window in the VBA editor.

Press ALT+F11 to open the VBA editor. If the Immediate Window is not already visible, press CTRL+G to open it.

Immediate window in the VBA editor

Valid VBA commands typed into the Immediate Window get executed after you press the Return/Enter key.

So I can put the value 101 into cells A1 to A10 on the active sheet with

Entering values into sheet from immediate window

and to fill 100,000 cells with random numbers

entering formulae into sheet using immediate window

grouped running totals in power query

More Power Query Custom Functions Posts

delete empty rows and columns using power query

Remove Blank Rows and Columns from Tables in Power Query

Delete blank rows and columns from tables using Power Query. Even rows/columns with spaces, empty strings or non-printing whitespace
remove text between delimiters power query

Remove Text Between Delimiters – Power Query

Remove all occurrences of text between delimiters. There's no in-built Power Query function to do this, but this code does.
quickly create running totals in power query

Quickly Create Running Totals in Power Query

Create running totals in Power Query using super fast queries that can process 10's of 1000's of records in the blink of an eye.
power query custom functions

Power Query Custom Functions

Create functions in Power Query to provide functionality that isn't built in - from simple things like currency conversions to scraping websites, and more.

More Power Query Posts

Power Query if Statements incl. Nested ifs, if or, if and

How to write Power Query if statements, including nested if, ‘if or’ and ‘if and’, which are easier to write than their Excel counterparts.
power query variables

Power Query Variables 3 Ways

Power Query Variables enable you to create parameters that can be used repeatedly and they’re easily updated as they’re stored in one place.
delete empty rows and columns using power query

Remove Blank Rows and Columns from Tables in Power Query

Delete blank rows and columns from tables using Power Query. Even rows/columns with spaces, empty strings or non-printing whitespace
extracting data from lists and records in power query

Extracting Data from Nested Lists and Records in Power Query

Learn how to extract data from lists and records in Power Query, including examples where these data structures are nested inside each other.
combine files with different column names in power query

Combine Files With Different Column Names in Power Query

Learn how to load data into Power Query when the column names in your data don't match up. Sampe files to download.
power query keyboard shortcuts

Power Query Keyboard Shortcuts to Save Time

Time saving keyboard shortcuts for Power Query that work in both Excel and Power BI. Download the free Shortcuts eBook
remove text between delimiters power query

Remove Text Between Delimiters – Power Query

Remove all occurrences of text between delimiters. There's no in-built Power Query function to do this, but this code does.
power query advanced editor tips

Tips for Using The Power Query Advanced Editor

Tips for using the Power Query Advanced Editor in Excel and Power BI. Watch the video to see these tips in action
pivot unknown variable number of rows to columns

Pivot an Unknown Number of Rows into Columns

How do you pivot rows to columns when you don't know how many rows you're dealing with? It's not as easy as you may think.
try otherwise power query iferror

IFERROR in Power Query Using TRY OTHERWISE

Using TRY..OTHERWISE in Power Query Replicates Excel's IFERROR So You Can Trap and Manage Errors In Your Queries.


Category: Power QueryTag: Power Query Custom Functions
Previous Post:Power Query custom data typesPower Query Custom Data Types
Next Post:Introduction to Power Querypower query

Reader Interactions

Comments

  1. Jasper

    March 4, 2023 at 10:44 pm

    Great function!
    I want to use this with cumulative totals of sales of product A and B per quarter. But in some quarters we don’t have sales for product B and the function returns a cumulative total of 0. How can I fix this?

    Reply
    • Philip Treacy

      March 8, 2023 at 11:53 am

      Hi Jasper,

      hard to say without seeing your data. Please start a topic on our forum and attach you file so i can take a look.

      Regards

      Phil

      Reply
  2. Eric

    September 2, 2022 at 8:46 am

    Hello Phil, this is an excellent tutorial!

    Would it be possible to extend the function to allow me to group on more than one column?

    Say we have: [Town, Store, Item, Price] as my columns and I want to group on [Town, Store] instead of just Town or Store when performing a grouped running total on Price.

    As of this writing the function allows for one column only to be specified as the grouping column.

    I’m thinking there has to be a way to extend it to allow additional columns to be grouped; any suggestions on this?

    Thanks!

    Reply
    • Philip Treacy

      September 4, 2022 at 4:34 pm

      Thanks Eric.

      Could you achieve this by first sorting your table by Town and then Store, and then calculate the running total using the Store column?

      If that doesn’t work for you I may need to see your data and an example of exactly what you are after. You could start a topic on our forum and attach a file with sample data for me to look at.

      Regards

      Phil

      Reply
      • Eric

        September 8, 2022 at 8:48 am

        Thanks Phil,

        Concievably this should work as all of the stores would be ordered by town before applying your grouped running total. Since the logic detects differences in the grouping the counter in the code should reset before moving on to the next town. An edge-case would be if one of the towns had a store at the end of its list with the next town having the same store at beginning.

        A fictitious scenario to illustrate:

        Town Store Item Sales Running Total Comments
        Athabasca McLeods fishing pole 50 50
        Athabasca McLeods tackle box 40 90
        Athabasca Zellers bowl 3 3 3
        Athabasca Zellers cup 2 2 5
        Melville Petco cat food 5.5 5.5
        Melville Petco fish food 7 12.5
        Yorkton Petco cat food 6 18.5 Running total continues to count despite stores being in different towns.
        Yorkton Petco dog food 5 23.5 Running total continues to count despite stores being in different towns.
        Yorkton Zellers plate 2 2 2
        Yorkton Zellers spoon 1 1 3

        Since Petco is in both Melville and Yorkton the logic does not detect that this store is in different towns.

        Is there a better way to code the function to prevent this behaviour?

        Thanks again for your help on this!

        Eric

        Reply
        • Philip Treacy

          September 15, 2022 at 3:28 pm

          Hi Eric,

          You can supply the Town and Store columns as a single composite (key) column to the function, using List.Zip

          fxGroupedRunningTotal(Data[Sales], List.Zip({ Data[Town], Data[Store] } ))

          Regards

          Phil

          Reply
  3. Gab

    August 25, 2022 at 11:40 am

    Hello,
    I am having problems trying to implement the function in my dataflow in power bi service; Since I have to apply it to several columns, I have generated the lists of each column in power query and finally, I have called the function for each of them in a single function, something like this;
    .
    .
    .
    listCategory_1 = List.Buffer(Source[Category_1]),
    listValues_2 = List.Buffer(Source[Values_2]),
    listValues_3 = List.Buffer(Source[Values_3]),
    …
    listValues_n= List.Buffer(Source[Values_n]),
    RT = Table.FromColumns(
    {
    Source[Category_1], Source[Values_2], Source[Values_3]…Source[Values_n],
    fxsum(listValues_2, listCategory_1), fxsum(listValues_3, listCategory_1)…fxsum(listValues_n, listCategory_1)
    },
    {
    “Category_1”, “Values_2”, “Values_3″, …”Values_n”, “SumValues_2”, “SumValues_3″, …”SumValues_n”
    }
    )
    in
    RT

    Everything seems fine in the first calculated rows, even the data flow can be saved but when updating completely I get the following error:
    Error: The field ‘1’ of the record wasn’t found. Request ID etc…

    It might be necessary to add a step in the function that returns the value of i=1 at the end..??

    Reply
    • Philip Treacy

      August 28, 2022 at 3:08 pm

      Hi Gab,

      Really hard to debug without seeing the data. have you made any change to the function?

      What record/data is being referred to in the error?

      Can you try narrowing down the cause of the problem by passing 1 list of values into the function at a time?

      Regards

      Phil

      Reply
  4. Charl

    December 20, 2021 at 2:22 pm

    Hi,

    Thanks for excellent article:
    Any thoughts on below:

    (1) Would it be better to apply List.Buffer inside the function rather than calling code – that way function is more self contained and if used in multiple scenarios you wont need to remember to buffer each time in the calling code.

    (2) Rather than rely on the try / otherwise error check logic would it be cleaner to check that the counter doesnt exceed range with an if statement?

    Thanks
    – Charl

    Reply
    • Philip Treacy

      December 22, 2021 at 10:56 am

      Thanks Charl.

      1. Yes you could do it that way.

      2. You could try writing that but it might end up being a bit more complicated/require more lines of code. I’m using try .. otherwise because at some point grouping{[i] + 1 will exceed the number of elements in the list and generate an error. It also tests if the grouping changes, so it’s acting as 2 tests in 1. You’d need to accommodate both of these tests in your if.

      Regards

      Phil

      Reply
  5. Chris

    August 26, 2021 at 10:57 pm

    Based on the set up it only works when the source list is sorted by the grouping value. Would it not also work if the GRT counter is GRT&”-“&grouping{i} and than delete the “GRT = values{[i] + 1},” in the else loop?

    It does not work at the moment in my case but you might have a solution to set all GRT&”-“&grouping{i} variables to 0.

    I would rather not sort for the grouping since I need the original sorting for other steps.

    Thank you!!

    Reply
    • Mynda Treacy

      September 1, 2021 at 3:56 pm

      Hi Chris,

      Sorry I’m not following what changes you are proposing to make to the code. Have you tried what you suggest and gotten it working?

      The idea of a grouped running total is that the total is carried on for each subsequent value in the grouping. If you don’t sort the values by the grouping, how do you calculate your RT and make it readable?

      Could you do the sorting, calculate the RT, then unsort? You could use an index column to to this.

      Regards

      Phil

      Reply
  6. Brian

    August 19, 2021 at 6:19 am

    Absolutely Brilliant! Thank you!

    Reply
    • Mynda Treacy

      August 22, 2021 at 2:30 pm

      Glad it was helpful, Brian!

      Reply
  7. Daria

    August 16, 2021 at 2:42 am

    Hello,

    could you please advise what to do with 0 values in my value column. I can’t remove them, because i make the grouping by month and it is important to know that in this month the value was 0. But I receive the error that 0 values are not allowed for the List. Should I use another Type of Object for values?

    (Expression.Error: The value “0” cannot be converted to the type “List”.
    Details:
    Value=0
    Type=[Type])

    Thanks, Daria

    Reply
    • Philip Treacy

      August 16, 2021 at 9:06 pm

      Hi Daria,

      My code can cope with 0, or any numeric value, in the values column. So I’m not sure why you’re getting that error.

      Can you please start a topic on our forum and attach your file so I can take a look.

      Regards

      Phil

      Reply
      • Daria

        August 17, 2021 at 10:35 pm

        Hi Phil, thank you for your reply. I found the error and it’s working with 0 too.

        Regards
        Daria

        Reply
        • Philip Treacy

          August 18, 2021 at 9:19 am

          Great, glad you got it fixed.

          Reply
          • GFavaretto

            October 25, 2021 at 11:31 pm

            Hello!
            I have the same error: can you give some hints about how to solve? is there a topic in the forum that can be linked here?

            Thanks a lot!!

          • Philip Treacy

            October 26, 2021 at 10:55 am

            Hi,

            I don’t know what was wrong with Daria’s code as he said her fixed it. So to help with your s I’d need to se your code. Please start a topic on the forum and attach your file.

            Regards

            Phil

  8. Tomek

    July 28, 2021 at 4:32 pm

    I have a question about the M code:
    how does the “i” between square brackets – “[i]” differ from “i” without square brackets?

    Reply
    • Philip Treacy

      July 28, 2021 at 10:24 pm

      Hi Tomek,

      [i] is the current value of the variable i.

      i is the new value that you are creating in this current loop.

      So if [i] is 1 then i = [i] + 1 means create a new value for i that 1 + 1.

      Regards

      Phil

      Reply
  9. Eric Shine

    June 25, 2021 at 2:11 am

    I follow both the running totals tutorial and the grouped running totals tutorial and have greatly improved the speed of the loading tables. Thank you so much. Great tutorial.

    Reply
    • Catalin Bombea

      June 25, 2021 at 5:02 pm

      Thanks for feedback, glad to hear it works for you.

      Reply
  10. David McKenzie

    June 17, 2021 at 12:06 am

    Just a comment on the Pro Tip – you can do the same thing without VBA, just select the range you want (my personal way would be to type A100000 in the name box to go to that cell then Ctrl+Shift+UpArrow), then type the formula =RAND()*10, then press Ctrl+Enter to fill all the cells with that formula.

    Personally I’d also use =RANDBETWEEN(1,10) which gives random integers only, but it depends on what you want your fake data to be.

    Reply
    • Philip Treacy

      June 17, 2021 at 12:39 pm

      Thanks for the tip David.

      Reply
  11. Giova

    March 29, 2021 at 4:40 am

    I think I love you

    Reply
    • Philip Treacy

      March 29, 2021 at 8:45 am

      Thanks 🙂

      Reply
  12. martin

    March 9, 2021 at 1:29 pm

    awesome! please ,how to make it to calculate running percentage to total?

    Reply
    • Mynda Treacy

      March 10, 2021 at 8:57 am

      Hi Martin,

      You should do this in a PivotTable using the Show Value As tools.

      Mynda

      Reply
  13. Peter van der Hoeven

    February 20, 2021 at 3:07 am

    This content is extremely helpful so please don’t take this the wrong way. I was working through this article and trying to understand the logic of the custom function. In this section, item 2, it says “Keep looping until i is less than the number of items in the values list.” Should it say “Keep looping while i is less…”.

    Reply
    • Philip Treacy

      February 22, 2021 at 10:51 am

      Yes it should, thanks for picking that up Peter, I’ve corrected it now.

      Regards

      Phil

      Reply
  14. SUNBOY MOYO

    February 3, 2021 at 3:37 am

    How do we calculate running debit and credit balances with power query according to customer name

    Reply
    • Mynda Treacy

      February 3, 2021 at 9:00 am

      If this tutorial doesn’t explain the process according to your needs, please post your question on our Excel forum where you can also upload a sample file and we can help you further.

      Reply
  15. Jonno

    January 14, 2021 at 7:36 am

    Far out, this is brilliant, works very well indeed on my 26,000 row dataset.
    It’s amazing how powerful List.Generate is!

    Many thanks Mynda 🙂

    Reply
    • Mynda Treacy

      January 14, 2021 at 8:59 am

      Glad it was helpful, Jonno!

      Reply
      • Benoit Brochu

        June 14, 2021 at 9:23 am

        Thank you so much for the example , very useful and very well explain.

        Reply
        • Philip Treacy

          June 14, 2021 at 9:41 am

          Thanks Benoit, glad to help.

          Reply
        • Mynda Treacy

          June 14, 2021 at 9:49 am

          Great to hear, Benoit!

          Reply
  16. keivan

    November 24, 2020 at 9:05 pm

    Hi,
    I need exact above article except that I need the running total (in groups) sums the last 25 days.

    (Running the last 25 days Total, in Groups).

    Would you please help me for that?
    Thanks

    Reply
    • Mynda Treacy

      November 24, 2020 at 9:06 pm

      Hi Keivan,

      Please post your question on our Excel forum where you can also upload a sample file and we can help you further.

      Mynda

      Reply
  17. bita

    October 8, 2020 at 6:43 am

    hi Mynda

    Do you do one one training through zoom by any chance? I need training just on vlookup for my work and for years nobody has been able to explain it to me. byermian@bxala.com. thank you very much . I’m in the US

    Reply
    • Mynda Treacy

      October 8, 2020 at 10:13 am

      Hi Bita,

      I don’t do one on one training, but I recorded a video on VLOOKUP just a couple of weeks ago and a few people have told me that until watching my video they’d never been able to get their head around it. Please try watching the video and if you’re still stuck, please post your question on our Excel forum where you can also upload a sample file and we can help you further.

      Mynda

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.