Add Running Total for each Item

[Emily_Kim]

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 function.

Appreciate if someone in this forum can help me. Thank you very much.

Item	STAGE	TIME	TOTAL TIME
CX229	A	5	5
CX229	B	10	15
CX229	C	15	30
DA051	A	7	7
DA051	B	8	15
DA051	D	3	18
DA051	E	6	24
DA214	B	9	9
DA214	C	15	24
DA214	G	5	29
DA214	H	3	32

[AlienSx · Accepted solution]

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

[shafiz_p · Accepted solution]

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

[Omid_Motamedise · Accepted solution]

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"

[shafiz_p · Accepted solution]

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

[AlienSx · Accepted solution]

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