Forum Discussion
Emily_Kim
1 year agoFrequent Visitor
Add Running Total for each Item
Hello Everyone, I need to add a column "Total Time" as running total for the "Time" column until the next Item change. I think I can do this with List.Accummulate but I'm not sure how to use this fu...
- 1 year ago
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], data = List.Buffer(Table.ToRows(Source)), gen = List.Generate( () => [i = 0, item = data{0}{0}, total = data{0}{2}], (x) => x[i] < List.Count(data), (x) => [i = x[i] + 1, item = data{i}{0}, total = data{i}{2} + x[total] * Number.From(item = x[item])], (x) => data{x[i]} & {x[total]} ), tbl = Table.FromRows(gen, Table.ColumnNames(Source) & {"TOTAL TIME"}) in tbl - 1 year ago
Hi Emily_Kim Go to power query and add a index column. See image below:
Go to home tab and click advance edition and add the below marked line. Using List.Sum and List.FirstN function you can acheive your desired result. See image below:
You will get your desired output. See image below:
Now you can remove index column and change type of running total.
Hope this helps!!If this solved your problem, please accept it as a solution!!
Best Regards,
Shahariar Hafiz - 1 year ago
Check this one step code:
let Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content], X = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type), #"Added Custom" = Table.AddColumn(X, "Custom", each List.Sum(Table.SelectRows(X, (a)=> a[Index]<=[Index] and a[Item]=[Item])[TIME])) in #"Added Custom"
Omid_Motamedise
1 year agoSuper User
Check this one step code:
let
Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
X = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(X, "Custom", each List.Sum(Table.SelectRows(X, (a)=> a[Index]<=[Index] and a[Item]=[Item])[TIME]))
in
#"Added Custom"