Forum Discussion

Emily_Kim's avatar
Emily_Kim
Frequent Visitor
1 year ago
Solved

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

 

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

 

ItemSTAGETIMETOTAL TIME
CX229A55
CX229B1015
CX229C1530
DA051A77
DA051B815
DA051D318
DA051E624
DA214B99
DA214C1524
DA214G529
DA214H332
  • 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
  • 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

  • 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"

3 Replies

  • 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"
  • 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
  • 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