67 - Cumulative sum, List Generate and function scope
How to create a cumulative sum-function in Power Query, and all kinds of optimization/smart stuff related to it. Including making the function exit once a condition is met (e.g. once cumulative sum of sale reaches X amount).
I also go through the List.Generate function and talk about function scope.
I use 3 kinds of scopes:
Inner function/Lambda (function exists within the step that uses the function)
Inner query (the function is a step within the query that uses the function)
Global/Outer function (the function is a query in itself, and can be called by any query)
00:17 Can't simply add a calculated column
00:58 Extract the column as a list
01:14 List.Buffer
01:50 List.FirstN
02:27 List.Sum
02:44 Everything in one step
03:09 Adding a filter
03:56 Filter in one step
04:39 Adding a variable
05:11 Minimizing calculations
05:55 List.Generate is like a Do While-loop
07:55 Logic of the first loop
08:43 Changing the looper variable
09:33 Explaining List.Generate part 2
10:33 I got IntelliSense to work with my function
11:04 Trying to add help to IntelliSense (fail)
11:32 I tried adding description to the step
12:01 Creating an Inner function / Lambda
13:11 What the Advanced Editor looks like with inner functions
13:38 Inner function part 2
14:54 Global function
15:08 A parameter can't be a list...
16:55 Adding the filter
17:44 Table.FirstN
18:09 Doing a lookup to add the cumsum-column
18:23 Calling the function (BAD)
18:50 Lookup
19:19 Apparently lists are also index zero
Watch video 67 - Cumulative sum, List Generate and function scope online without registration, duration hours minute second in high quality. This video was added by user WesleySon 10 December 2023, don't forget to share it with your friends and acquaintances, it has been viewed on our site 30 once and liked it 0 people.