Customized running total in Excel power query

[sara1312]

Please find the attached table

Group	SKUS	Hours of production run	Target hours	Index	Running Total	Example Colum
Group 1	1	23	36	1	23	23
Group 1	2	4	36	2	27	4
Group 1	3	3	36	3	30	3
Group 1	4	10	36	4	40	6
Group 1	5	23	36	5	63	Not Eligible
Group 1	6	13	36	6	76	Not Eligible
Group 1	7	16	36	7	92	Not Eligible
Group 1	8	31	36	8	123	Not Eligible
Group 1	9	13	36	9	136	Not Eligible
Group 1	10	15	36	10	151	Not Eligible
Group 1	11	23	36	11	174	Not Eligible
Group 1	12	72	36	12	246	Not Eligible
Group 1	13	11	36	13	257	Not Eligible
Group 1	14	5	36	14	262	Not Eligible
Group 1	15	62	36	15	324	Not Eligible
Group 1	16	20	36	16	344	Not Eligible
Group 1	17	64	36	17	408	Not Eligible
Group 2	18	22	36	18	22	22
Group 2	19	10	36	19	32	10
Group 2	20	83	36	20	115	4
Group 2	21	24	36	21	139	Not Eligible
Group 2	22	65	36	22	204	Not Eligible
Group 2	23	10	36	23	214	Not Eligible

Group 1 hours of production run to be running totaled but when its above 36 hours return the hours to add up to 36 , the remaining skus are not eligible for the calculation

Kindly support on how we can achieve this

 

[sara1312]

Can you post your query?

Hi , I used the below
The issue is when change to the new source , this custom step is not capturing the data. It stay with the initial file.
The new source table name is also "SCHEDULE" FYI.
I created a blank query to map the previous as a work around , but I would like to have the source fetched from the previous step or I need a solution to overcome this

= let
Source = Excel.CurrentWorkbook(){[Name="SCHEDULE"]}[Content],
Group = Table.Group(Source, {"GPRS Code"}, {{"All", each
let tbl = Table.RemoveColumns(_, {"Threshold"}),
tbl1= Table.AddIndexColumn(_, "idx"),
tbl2= Table.AddColumn(tbl, "Threshold", each
if [RT] <= 36 then [Hours] else
if [idx] = 0 then 36 else
if tbl[RT]{[idx] -1} < 36 then 36 - tbl[RT]{[idx] -1} else
"Not Eligble"
)
in
tbl1
}}),
Result = Table.RemoveColumns(Table.Combine(Group[All]), {"idx"})
in
Result

 

[JGordon11]

So if have a query already like below ending with StepN (change to whatever your last step is named) then have my code reference that last step

let
    Source = ...
    Step1 = ...
    Step2 = ...
    ...
    StepN = ...
    Group = Table.Group(StepN, {"GPRS Code"}, {{"All", each
        let tbl = Table.RemoveColumns(_, {"Threshold"}),
            tbl1= Table.AddIndexColumn(_, "idx"),
            tbl2= Table.AddColumn(tbl, "Threshold", each
                if [RT] <= 36 then [Hours] else
                if [idx] = 0 then 36 else
                if tbl[RT]{[idx] -1} < 36 then 36 - tbl[RT]{[idx] -1} else
                "Not Eligble")
        in
            tbl2
    }}),
    Result = Table.RemoveColumns(Table.Combine(Group[All]), {"idx"})
in
    Result