Forum Discussion
Helper IRunning Total for each Group / ID
Use this. Replace ??? appropriately
let Source = ??? BuffNumberList = Source[Number], BuffIDList = Source[ID], GenListOfRunningTotal = List.Generate(()=>[x=BuffNumberList{0},y=0], each [y]<List.Count(BuffNumberList), each [y=[y]+1, x=if BuffIDList{y} = BuffIDList{[y]} then [x]+BuffNumberList{y} else BuffNumberList{y}], each [x]), Result = Table.FromColumns(Table.ToColumns(Source) & {GenListOfRunningTotal},Table.ColumnNames(Source)&{"Running total per group"}) in Result
Most Valuable ProfessionalUse this. Replace ??? appropriately
let
Source = ???
BuffNumberList = Source[Number],
BuffIDList = Source[ID],
GenListOfRunningTotal = List.Generate(()=>[x=BuffNumberList{0},y=0], each [y]<List.Count(BuffNumberList), each [y=[y]+1, x=if BuffIDList{y} = BuffIDList{[y]} then [x]+BuffNumberList{y} else BuffNumberList{y}], each [x]),
Result = Table.FromColumns(Table.ToColumns(Source) & {GenListOfRunningTotal},Table.ColumnNames(Source)&{"Running total per group"})
in
Result
Helper IThank you for the response, this works great. If you don't mind, I had come up with something sort of similar as a blank query.
(values as list, ID as list) as list=>
let
RT =
List.Generate(
()=>[RT = values{0},counter = 1],
each [counter]-1 < List.Count(values),
each (if ID{[counter]-1}=ID{[counter]}
then [RT = [RT]+values{[counter]},counter=[counter]+1]
else [RT = values{[counter]},counter=[counter]+1]),
each [RT])
in
RT
However, on this line "each [counter]-1 < List.Count(values)," I get the error message: 'An error occurred in the ‘’ query. Expression.Error: There weren't enough elements in the enumeration to complete the operation.
Details: [List]', and another line is added to the table just saying error for each column.
If I change the line to: " each [counter] < List.Count(values)," then the last value in the RT (running total) column is null. I am not sure of a way around this, do you have any suggestions please?