Fast Running Totals in Power Query (Complete Guide)

Hey Rick! thank you so much for sharing this info, helped me a lot with a project I was stuck on for a few days. Kind regards!

What a great video. I think this will really help time series calculations in my dashboards

Thank you for the clear explanation ! :)

Great series of videos, I am currently trying to see if I can take some of these principles to improve my Grouped Rolling Total Values calculation speed. Rolling total is slightly different to the running total described in your video, as I am interested in previous 3,6,9,12 months etc. Currently I am using List.Sum with Greater Than and Less Than conditions, but with a couple thousand rows this takes very long time (1+hr to calculate). Could not find existing Power Query video on this topic, but if you have one, please point me in that direction.

💯👍

Does this work even if the columns I'm dealing with are calculated columns?

Great videos. Keep ‘em’ coming 👍

Great video Rick, very informative and easy to follow, even for a beginner like me, thanks

Hi. Can you help me with the following. I want to to create a column that looks something like this. IF (Column name) >=20230601 but <=20230631 then 1 else 0. How can i create a column where something within a range should become 1 and all the rest should be 0. I have really tried andr tried without any luck, can you show me how?

Hey can you please make a video on how to calculate running count via calculated column measure in powerbi

Thank you very much for sharing it.

Explanation is good, however, the need for simple lany. 😊

Never thought that was a "buffered" function. I really like you "coding" solutions.
But I have a question... Why you didn't use calculate, then filter by allselected dates? does this use more resources on a larger scale?. I'm mean the "calculate" is way shorter and simpler.
Great video.

I am doing a running total for inventory forecasting. Each location has a Max inventory allowed and Min inventory allowed. How can I do a Running Total for each category, where each category has its own Min/Max limit? For example, inventory cannot exceed 2200 and cannot be below 200 for location "A", whereas location "B" cannot exceed 500 and cannot be below 50. This Min/Max is listed in another column that can be referenced for each category/group. I have the running totals working for each group but cannot seem to figure out how to incorporate min/max limits. Can you help?

Great explanation and guidance,
I have applied your step everything look great, but when I load the table to sheet the Running Total Column showing Blank with out any value where the value already existing in the query, can you please help my in this issue.
Manty Thanks

I'm a beginner with PQ but was able to follow you well. Great and clear explanation. You have a fan :) thank you.

Incredible video. I learned List.Generate recently. Didn't know I could iterate like I was writing a loop in python.
List.Generate is way more powerful than I thought.

& i love the List.Buffer thing. Hats Off!

Very Impressive!

Espectacular esta lección de Power Query, gracias !!!

Absolutely fantastic - Clear in thought, explanation, voice, demo & samples, Thanks a ton

Thank you so much for creating this amazing Video on running total. #1 tutoring on running total.

This is crazy. For years I've been duplicating queries, removing columns, doing GroupBy, and Sum, and then pulling the totals back to my original dataset with a merge. I've created so many unnecessary tables. This is a godsend for me. Thanks a million!

Great great content! Congratulations

Here is my take on this, using an index column to merge the two lists:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Number", Int64.Type}}),
Number = #"Changed Type"[Number],
Custom1 = List.Generate(
()=> [RT = Number{0}, Counter = 0],
each [Counter] < List.Count(Number),
each [RT = [RT] + Number{[Counter]+1}, Counter = [Counter]+1],
each [RT]
),
#"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), {"Running Total"}, null, ExtraValues.Error),
#"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Number", each Number{[Index]}),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Number", "Running Total"})
in
#"Removed Other Columns"

This all should be doable via gui. That's one thing missing in Power Query.

Wonderful video Rick! I went over your blogs on PQ, Excellent content! Thank you very much for that effort and very simple explanation.

This video was great cleared all my concepts.Can you share a video on consolidating files with changing column headers especially when there are many columns.

Great content.. I recently switched to list.generare. And I do see performance improvement. But my next challenge in power query is to do FIFO tables, where for each material there is sales and purchases. Doable but very challenging. If you run out of content, please consider how to do FIFO using power query.

What a great video!

Tons of valuable content!

Saved in the learning playlist!!

Brilliant!!!

That magic index column.🤔

Really great video, I found Generate hard, and still haven't been able to get the data type to work,
I get the error " can't convert list to type", any chance of doing a bit more on the accer method, I've been using it for ages without really understanding it, this is a bit different to your method;

List.Accumulate( Source [ Unit], {0 } , (A, B )=> A & {List.Last(A) + B } )

I've not been able to isolate the ampersand to see what it's doing. Lastly and alternative to your ingenious method of joining Source to list , just add column with an Index as place holder . More curly brackets.

This was excellent thanks Rick. I have learned so much from you so far already. Please keep on creating this excellent content!

Thanks for pointing out that null-trap and how to avoid it. Great stuff! :-)

Thank you for your time and solution to the problem that you expose. Greetings from Chile.

Superb!, Thanks for sharing!

Hi Rick!
Super usefull, thank you!

P.S. What about the cherry on top - code encapsulated into function like AddRunningSum(TableName, inColumnName, outColumnName)
?

Hi Rick, this is a great way of doing it. I was just wondering if you have multiple products and want to see the running total per product, how would you calculate that? Can you group it dynamically?

excellent video & well explained as always