Power Query Running Totals - The Right Way! 

Wow. That's all really impressive. I comprehended only parts of it, so I will download the file for future use because I would have no chance of ever re-creating it. My question is why doesn't MS just create a Running Total function and put it in the list of MCode functions and in the ribbon?

Thank you very much for this video. Though I find it troubling that something as simple as a running total requires so much effort in PQ. In Python, it is simply cumsum() and you're done.

Hi, Great Video - very helpful. I have an expanded request based on this. When we have the running total I need a column that shows the max of the previous 5 days totals, and that again is a running total, so everyday show me the max of the previous 5 days rolling total. This is for PNL and so the rolling total goes up and down on a daily basis. In a normal spreadsheet that is easy, but in Power Query in Excel I can’t get the logic. It should be close ish to what you have done here? Any idea anyone on the formular/setup I need to achieve that?

Cannot we wrap up the source with List.Buffer and then apply the List.FristN and List.Sum? List.Sum(List.FirstN(List.Buffer(Source[Amount],[Index]))) something like this?

You lost me right at the end but I love it. Thank you. Would you be able to do a video where the RT restarts with one or multiple criteria’s?

gREAT!!! Supossed we have another column dividing the Value column..... How can I do this exact path but with categorical group?

Hi, great video, I'll probably have to watch it 100 times, hopefully something will catch on in the end.... What if I want to make a running total for 2 or 3 columns? I messed up...

Hi Mynda. Thanks for the advanced lesson on running totals in Power Query. Always more to learn and practice :)) Thumbs up!!

at ~11:08 you show that the query still executes 'instantly' after increasing the filtered row count from 5,000 to 100,000. My understanding was that the query doesn't actually execute until you load the data; and that's when you can compare performance. Everything you see in the PQ editor is being performed on a preview of the dataset. Am I wrong in this belief and actually the query is being performed on the whole dataset live in the PQ editor?

Hi Mynda. I know this is an old video but I've only just come across it in trying to find a good way to add any kind of window column to a table... great stuff! Thank you. The thought occurs - given there's no consideration for referencing an index column of the original table, could you use the List.FirstN function inside List.Generate, and reference the current iteration using _? Like: List.Generate( () => 1, each _

Hi, I try to use the same formula, but it just keep saying "Added Indedx" wasnt recognised.

Excellent video Mynda. Thanks a lot for sharing your job.

Hi Mynda...what if I need to calculate RT on more than one "value" columns from one table?

I like using power query over dax a lot becuase while it makes the updating and adding data take longer it makes the dashboard faster for the users.

Thanks 🙏 , but if we need to be like sumifs Accumulated balance depends on multiple criteria

Amazing!!! I comprehended only parts of it, so I'll download the file for future use and learn too. Thanks for sharing

Really interesting thanks. I have a different problem: I need to check the sum of each employee's working hours per month, against a monthly contracted number of hours. So I require a running total (and monthly reset) of a dynamic number of employees. This is because I'm doing "point in time" analysis, not just looking at monthly aggregates. I'm dimly aware that this might require use of a date (calendar) file, in order to "move along". Do you cover that sort of thing anywhere, or could you point me in a direction?

Excellent, thank you! One question, though: at 4:48 you are saying "[Index] list...". Why "list"? why isn't it just the value in the [Index] column of the current row?

Great though there is hardly only one category in the data, so let's say there is a 'product' category and its sales per day, how do you reset the count for each product category or each date category (weeks or months)?

Gosh - that's very advanced. But I don't know anything about that programming language you were using. What is that language? Is it part of Power Query? I think I got lost along the way somewhere. Many thanks.

Hello Mynda, thank you so much for your videos, I had learn so much from your video in the past couple of years! I do have a question for you on this running total, I used this method in one of my Power BI, and it had been working great, however, my analysis is getting more complex and I had to run the running total function over 5 times within the same query, and right now my query takes over an hour to load. I am wondering if I have to clear the buffer before I use the same rolling total function again? Thank you.

Thanks Mynda & Phil for the code - great content as usual

how do you do if you want to sum january to december and then a new year start again cumulative?

Really great. It's complex but you made it easy to follow. Thanks so much!

It is a good practice! how to do running total with 2 or 3 conditions. It is the same function SUMIFS in excel. Thanks a lot!

Hi Mynda Tks for the info. I encounter issue with power query I wanna set auto refresh on both pivot table and power query The data used in the pivot table is the output of power query. After i set an VBA workbook.refreshall on the source data sheet, and disable the background refresh, everytime I open the worksheet, it just continously non stop refreshing the workbook which I forced me to end the application. I wonder if I did anything wrong? Thanks in advance

Wow!... Optimization is really the key to achieve this... and you explain exactly how to do it!.. Thank you so much!!

Hi .. great tip... I initially used DAX for running total in a Power BI visual but due to huge data size, matrix visual was taking a long time to refresh whenever a filter is applied. So I thought to perform the calculation within Power Query to speed-up the report visual. I was able to create the column with running totals in Power Query, however, I have two additional columns; product and month. I need Jan-Dec running totals for each product. Is it possible using this method?

Loved it, thank you so much

Hi, Thank you for the video, it is amazing! I tried to used on query after merging and for some reason does not work, do we need some extra lines in the code?

If you nest the 'buffer' within LIst.FirstN will it work the same? so ; List.FirstN( List.Buffer(#"Added Index"[Units] ) ,[Index] ) ) , rather than, List.FirstN ( Bufferlist,[Index] ) ) , I can't see any reason why there should be a performacne issue, but i've been wrong before. 🙄🙄

Thanks a lot for making such wonderful videos. I did not know about List.Generate!!! Could not resist thanking you

You' re nice and your presentation nicer??

Great tutorial! How do you apply this if you have sub-categories?

Jeez, not sure I can say I'm an advanced excel user anymore. I'm quite behind the times on excel tools.

Excellent Very Good! Just what I needed... tks;👏

Omg this is too advanced and tough.

Great to see this work, but I can't help wondering why you wouldn't just load the data into a pivot table and add a running total column that way, which is significantly easier and doesn't have any of the speed or memory issues you talked about early on in the video. Maybe I'm missing something - please explain! Thank you.

Thanks, valuable video. After watching this video, I got an question. Is it possible to calculate cumulative percentage in power query?

My brain is full of carbon dioxide now

Hi Mynda, Thank you so much for the video. It's super useful :) Small help required ! I tried to calculate the running total based on the same logic explained. I was able to make my way through all the steps but on the RT step it returns me a NULL value for all rows. Not sure why ! *Just a note here - I have total three columns. Any thoughts ?

Super!! Pretty advance but this is a great way to get into this problem when millions of records have to be calculated. I'm working on using this technique on the scenario of having different products which need to get a running total for each one! Thanks a lot!

Thank you very much for your clear explanation. It helps me optimization of the current calculation. You are the best!

Thank you for this video, just solved my problem! And by adding several indexes, I can now run different slicers on running totals according to the users perspective on sorting😄

I get cyclic reference error

Hi Mynda. Thanks for this excellent tutorial. One issue I have since today (might have been from the weekend), my List.Buffer function now returns my list in a different order than the original column. This was working for the past month. I can't understand why it's failing now. Has something changed on Power BI? (it's in a dataflow in case that helps)

Finally got round to wrapping my head around List.Generate. Powerful stuff and very versatile. I agree: this is a linear effort formula for calculating a CumSum. This has to be the most efficient way... And that List.Buffer trick makes a huge difference, too. This is a great lesson!

This is "running totals" Deluxe:-) Thank you!

How to reset running total every year if data is year - monthly based?

Hi Mynda!Great Explanation Of The M CODE... Thank You And Thank You Phil :)

Dear Mynda, great explanation. Thank you. I have the tricky challenge to reverse a monthly running total (ytd values) to monthly values...if you have a nice solution in PQ you would help me so much:) Kind regards

List.Generate is genius! Thank-you so much Mynda and Philip.

You are awesome 😎

Really advanced and really need to understand the syntax. Thanks for sharing

You're a lifesaver Mynda. Thank you so much!

Thanks works brilliantly. For my data I want to have another running total in the same table based of another column but still linked by Country. E.g add a column = Value2 and have a RT for Value2 alongside the RT for Value. How do I fit that into the code?

Hi Mynda & Phil, Thanks for the excellent article & video - performance is surprisingly good. On some days my brain does struggle with the way Power Query utilises the each keyword and underscore conventions in functions like List.Generate. Below is my much more verbose version which I find a little easier to understand in case it is of interest to anyone.. (Values as list)=> let BufferedValues = List.Buffer(Values), fn_Seed = () =>[Counter=0, RunningTotal=BufferedValues{0}], fn_ContinueWhileTrue = (CurrentRecord)=> CurrentRecord[Counter] let NextRecord = [ Counter = CurrentRecord[Counter] + 1, RunningTotal = CurrentRecord[RunningTotal] + BufferedValues{Counter} ] in NextRecord, fn_ReturnValue = (CurrentRecord)=>CurrentRecord[RunningTotal], Output = List.Generate( fn_Seed, fn_ContinueWhileTrue, fn_GenerateNextValue, fn_ReturnValue ) in Output

I'm a seasoned Power Query User, but this is a lifesaver for current edge case! Thank you

hi thank you for knowlodge please show how create a trading plane dashboard like a pro ?

Thanks :)

What is the advantage of doing in power query instead of dax?. Great video BTW!

Hi Ma'm. Can you please make a detailed video on Excel Header and Footers? It would be great if that includes using VB for creating dynamic headers and footers.

What about by subgroups that long 5000 rows

Marvelous! Thanks, Phil, for the technique (enjoyed the blogpost). Thank, Mynda, for the demonstration.

Good👍👍

Thanks Mynda - that is really useful

Nice 👍

A bit too advanced for me but it was amazing nonetheless.

Great video and very useful. I think maybe @edsta714 means can you do running totals by category - ie by the value of another column (eg by product or geography or month). I realise you can do this in Pivot Table or DAX but if for some reason you want it in the Power Query step it would be useful

Thank You for the video, it is clear and straight to the point.

Microsoft must hate their users to necessitate anything like this. They must want users to use Python or SAS. Excel already has a running total feature in their pivot tables. So, I'd think the logic would be have been made more accessible here.

Great Mynda. Thank you 👍

Wow, I got lost but sound amazing , have to watch again and again

Bit advanced for me but very good

Wow, you lost me, but this was really great.

It was a great explanation!

Very impressive Mynda!

Wow Time Saver Thanx

you are the best Mynda !

what is #"Added Index" ?