Power Query List.Generate for Conditional Running Total AND Count (Step-by-Step)

This Conditional Running Total and Conditional Running Count task from Excel MVP Bhavya Gupta has 3 different challenges and conditions to be fulfilled. I solve this in Power Query using a custom function with List.Generate (recursive calculation).
👉 Custom functions can help you to solve complex problems.
👉 List.Generate allows to address various challenges together!
👉 Here 3 different values are generated simultaniously for the Running Total.

This video explains this advanced solution step by step: You learn how to use a Custom Function, List.Generate, try...otherwise statement, if...else statement and so much more.

Timestamps
00:00 Explanation of the Challenge
02:40 Import to Power Query Editor
03:24 Adapt the List.Generate Custom Function I
08:08 Apply the List.Generate Custom Function with Table.FromColumns
10:08 Adapt the List.Generate Custom Function II
14:01 Extract and Select the Results

#PowerQuery #Excel #PowerBI
Like 👍, comment 💬, and share with fellow data enthusiasts to spread the knowledge!

Ready to become a Power Query master? Join me, Matthias Friedmann, and learn advanced data techniques and solve complex Power Query challenges with ease:

[cedjulemckeever]

Nice explanation. I'm surprised that there are not more comments.

[MatthiasFriedmann]

Here is the code for the custom function and the query:
(values as list) as list =>
let
GRTList = List.Generate(
()=> [ GRT = values{0}, c = 1, Group Count = null, i = 0 ],
each [i] < List.Count(values),
each try
if [GRT] + values{[i]} <= 10000
then [GRT = [GRT] + values{[i] + 1}, c = [c] + 1, Group Count = try
if [GRT] + values{[i] + 1} + values{[i] + 2} > 10000 then c else null otherwise c, i = [i] + 1]
else [GRT = values{[i] + 1}, c = 1, i = [i] + 1]
otherwise [i = [i] + 1]
)
in
GRTList

The original custom function plus thorough explanations from Phil Treacy:


let
Source = Excel.CurrentWorkbook(){[Name="GRT"]}[Content],
GRT = Table.FromColumns(
{Source[Numbers]} & {fxRunningTotal( List.Buffer(Source[Numbers]) )},
{"Numbers", "Running Total"}
),
#"Expanded Running Total" = Table.ExpandRecordColumn(GRT, "Running Total", {"GRT", "Group Count"}, {"Running Total", "Group Count"})
in
#"Expanded Running Total"