I am trying to create a column that will reset the running total to 0 or the next value in the list when the running total value hits a specific number (10000 in this case)
So it is taking in a list of numbers, adding them I need to figure out why the validation of the current sum value isnt working.
fxRunningGrouping
(values as list) as list =>
let
RT = List.Generate
(
()=> [RT = values{0}, counter = 0],
each [counter] < List.Count(values),
each if {[RT]} >= 10000 then [RT = values{[counter]}, counter = [counter]+1] else
//each if _>= 10000 then [RT = values{[counter]}, counter = [counter]+1] else
each [RT = [RT]+values{[counter]+1}, counter = [counter]+1],
each [RT]
)
in
RT
Hi William,
Looking at:
each if {[RT]} >= 10000
The curly brackets indicate a list type, normally, you cannot compare a list to a number, you have to make sure you compare the same data types.
So I added a column that just has 10000 as the value would I be able to do something like
if [RT] >= [newColumn] then [RT = values{[counter]}, counter = [counter]+1] else ....
The idea is to reset the current total to the current value in the list. Thus resetting the total.
What happens if it doesnt reset is the addition doesnt come out right because the values can be anything.
Hi William,
Here is an example based on:
https://www.myonlinetraininghub.com/quickly-create-running-totals-in-power-query
>code>
(values as list) as list=>
let
RT = List.Generate
(
()=> [RT = values{0}, counter = 0],
each [counter] < List.Count(values),
each [RT = if [RT]>=10000 then 0 else [RT] + values{[counter] + 1}, counter = [counter] + 1],
each [RT]
)
in RT
If you don't want to reset to 0 but to the current row value (which makes more sense):
each [RT = if [RT]>=10000 then values{[counter] + 1} else [RT] + values{[counter] + 1}, counter = [counter] + 1],
Thanks that worked exactly the way I needed it to.
