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 fu...
  • AlienSx's avatar
    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
  • shafiz_p's avatar
    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

  • Omid_Motamedise's avatar
    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"