Log In
Register
Home
Active Member
July 10, 2021
Offline
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
See Answer
November 8, 2013
Offline
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.
Active Member
July 10, 2021
Offline
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.
November 8, 2013
Offline
Hi William,
Here is an example based on:
https://www.myonlinetraininghu.....ower-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],
Answers Post
1 Guest(s)
