[POWER QUERY] Grouped Running Totals with a maximum condition and a verifier

[SunProj3cT]

Good evening, people.

I'm in need of a function on power query that sums the running total across its categories, but compares the running total value to a previously defined maximum for its category. If running total is greater than its maximum, it subtracts its own value and remains the same as (i-1). In addition, it should add 0 to a verification column.

I used this code from myonlinetraininghub (https://www.myonlinetraininghub.com/grouped-running-totals-in-power-query) and it solves part of my problem, but not the entirety of it

Here's a screenshot of an expected result:

I would be very pleased if anyone could help me with this. It doesn't seem to be difficult to solve, but I've tried several different solutions and none have worked, so I'm having a hard time with this.

Thanks in advance

 

[JGordon11]

let
    tbl1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type1 = Table.TransformColumnTypes(tbl1,{{"Group", type text}, {"Value", Int64.Type}}),
    tbl2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Type2 = Table.TransformColumnTypes(tbl2,{{"Group", type text}, {"Max", Int64.Type}}),
    tbl4 = Table.AddColumn(Type1,"Max", each Type2{[Group = _[Group]]}[Max]),
    tbl5 = Table.Group(tbl4, {"Group"}, {{"All", each _, type table [Group=nullable text, Value=nullable number, Max=number]}}),
    tbl6 = Table.TransformColumns(tbl5,{{"All", each 
        Table.AddIndexColumn(_,"Index")}}),
    tbl7 = Table.TransformColumns(tbl6,{{"All", (ttc)=>
        let 
            lst1 = List.Skip(List.Generate(()=> [x=0,y=0], each [x]<= Table.RowCount(ttc), each [x = [x] +1, y = 
                if [y] + ttc[Value]{[x]} <= ttc[Max]{0} then [y] + ttc[Value]{[x]} else [y]
            ], each [y]),1)
        in
            Table.AddColumn(ttc,"Running", (z)=> 
                lst1{z[Index]}
            )}}),
    tbl8 = Table.ExpandTableColumn(tbl7, "All", {"Value", "Running"}, {"Value", "Running"}),
    tbl9 = Table.AddIndexColumn(tbl8, "Index", 0, 1, Int64.Type),
    tbl10 = Table.AddColumn(tbl9, "Verifier", each try if _[Running] = tbl9[Running]{_[Index]-1} and 
                                                            _[Group] = tbl9[Group]{_[Index]-1} and 
                                                            _[Value]<>0
                                                        then 0 else 1 otherwise 1),
    Result = Table.RemoveColumns(tbl10,{"Index"})
in
    Result

PQ Running Total.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Table1			Table2			PQ Output
2
3	Group	Value		Group	Max		Group	Value	Running	Verifier
4	A	4		A	47		A	4	4	1
5	A	15		B	49		A	15	19	1
6	A	3		C	120		A	3	22	1
7	A	27					A	27	22	0
8	A	12					A	12	34	1
9	A	11					A	11	45	1
10	A	23					A	23	45	0
11	B	10					B	10	10	1
12	B	20					B	20	30	1
13	B	33					B	33	30	0
14	B	2					B	2	32	1
15	B	45					B	45	32	0
16	C	1					C	1	1	1
17	C	54					C	54	55	1
18	C	5					C	5	60	1
19	C	43					C	43	103	1
20	C	32					C	32	103	0
21	C	1					C	1	104	1
22	C	1					C	1	105	1
23
24
Sheet1

 

[Bo_Ry]

Please try

let
    Source = Table.Join(Excel.CurrentWorkbook(){[Name="Table1"]}[Content],"Group", Excel.CurrentWorkbook(){[Name="Table2"]}[Content],"Group"),
    Runing = Table.Group(Source,"Group",{"A", each let 
      A = Table.AddIndexColumn(_,"i")
      in Table.AddColumn(A,"R", each 
        List.Accumulate(Table.SelectRows(A, (a)=> a[i]<=[i])[Value], [Running=0, Verifier = 1], 
          (s,l)=> [Running = if s[Running]+l >[Max] then s[Running] else s[Running]+l  , Verifier =Number.From(s[Running]+l <=[Max])]  ))
    }),
    ExpandedR = Table.ExpandRecordColumn( Table.ExpandTableColumn(Runing, "A", {"Value", "R"}), "R", {"Running", "Verifier"})
in
    ExpandedR

Book1
	A	B	C	D	E	F	G	H	I	J
1	Group	Value		Group	Max		Group	Value	Running	Verifier
2	A	4		A	47		A	4	4	1
3	A	15		B	49		A	15	19	1
4	A	3		C	120		A	3	22	1
5	A	27					A	27	22	0
6	A	12					A	12	34	1
7	A	11					A	11	45	1
8	A	23					A	23	45	0
9	B	10					B	10	10	1
10	B	20					B	20	30	1
11	B	33					B	33	30	0
12	B	2					B	2	32	1
13	B	45					B	45	32	0
14	C	1					C	1	1	1
15	C	54					C	54	55	1
16	C	5					C	5	60	1
17	C	43					C	43	103	1
18	C	32					C	32	103	0
19	C	1					C	1	104	1
20	C	1					C	1	105	1
Sheet1

 

[SunProj3cT]

Hi, guys. First of all: Thank you so much for helping me! Both solutions seem to work, but @JGordon11 's had some performance issues when I used the real database.

Second: I kind of underestimated the complexity between the operational database and this "dummy" example that I've shown to clarify my problem. So, when I tried to adapt it to my operational database it lacked some important data (such as request#id and sorting those requests with SLA and Total $) which made the result imprecise. I know I might be a lot sassy, but... is there any chance that I could send you a MP with a sanitized (but with real variables) database and a complete explanation for you to help me a little further @Bo_Ry ?