Fast Running Totals in Power Query (Complete Guide)

Running totals in Power Query can be fast, if you know how. This video shows different methods and analyzes why some perform better than others.

WRITTEN ARTICLE:

Master Functions and Syntax in M

ABOUT BI Gorilla:
BI Gorilla shares videos and articles on Power BI and Excel to help you improve your skills.

CONTENTS
00:00 Introduction
01:27 List.FirstN
05:02 List.Range
06:06 Performance Problems
10:47 List.Generate
17:33 Combine Running Total with Table
23:41 List.Accumulate

SUBSCRIBE TO MY CHANNEL

LET'S CONNECT:

Thank you for your support!

#RunningTotal #powerquery #bigorilla

[scottmorrison]

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!

[navisalomi]

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.

[ManojRR]

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

[JanBolhuis]

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

[teigenxayden]

At the end of the video, the method to deal with "null" is so great, it is a perfect solution.

[osoriog]

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!

[DoersGen]

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

[GeertDelmulle]

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

[jeromeastier]

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

[jhaanand]

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.

[RenierWessels]

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

[franciscom.paredesarias]

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

[azlanm]

Thank you for this video.. it really beneficial to me in constructing accounting general ledger.. thank you again

[themolestones]

Many thanks for such a great content.

From a rookie view, wouldn't be easier and "faster" to create the running total as a measure with DAX (Power BI or Power Pivot)?

In any case, I've learnt a lot.

Thanks.

[bagnon]

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" = 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"

[robertgreen]

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

[anatulymaimany]

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

[KevinRackliff]

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

[williamelliott]

excellent video & well explained as always

[SeneXeL]

What a great video!

Tons of valuable content!

Saved in the learning playlist!!