Cumulative Sum if previous row in another column is the same-CodePudding

I'm trying to do something that is simple in excel but I need it to be added in my query. I am trying to create a cumulative sum in a new column of time values in one column if a name column equals a previous row. Example of before and after below

Data Input Table

ID	Time
A	2
B	3
C	1
D	0.5
E	1
E	3
E	5
F	2
G	3
G	4
H	1

Table After Query

ID	Time	BeforeStart
A	2	0
B	3	0
C	1	0
D	0.5	0
E	1	0
E	3	1
E	5	4
F	2	0
G	3	0
G	4	3
H	1	0

Basically if column ID equals the row above itself then sum the time and BeforeStart rows above itself, if it doesn't then it is 0.

CodePudding user response：

In powerquery, try

let 
  xFunction = (xTable as table) as table => let
  #"Added Index" = Table.AddIndexColumn(xTable, "Index", 1, 1, Int64.Type),
  #"Added Custom" = Table.AddColumn(#"Added Index", "Running Total", each List.Sum(List.FirstN(#"Added Index"[Time],[Index]-1)))
  in  #"Added Custom",

 Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Time", type number}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"data", each  xFunction(_), type table }}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"Time", "Running Total"}, {"Time", "Running Total"})
in #"Expanded data"

CodePudding user response：

try this function:

IF(COUNTIF($A2:$A$2,$A2)=1,0,SUMPRODUCT((A2:$A$2=A2)*B2:$B$2)-B2)