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

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="YourTableName"]}[Content],
    Group = Table.Group(Source, {"Group"}, {{"All", each 
        let
            tbl = Table.AddIndexColumn(_, "idx"),
            tbl1 = Table.AddColumn(tbl, "Example", each 
                    if [Running Total] <= 36 then [Hours of production run] else
                    if [idx] = 0 then 36 else 
                    if tbl[Running Total]{[idx] -1} < 36 then 36 - tbl[Running Total]{[idx] -1} else 
                    "Not Eligble"
            )
        in  
            tbl1
    }}),
    Result = Table.RemoveColumns(Table.Combine(Group[All]), {"idx"})
in
    Result

 

[alex78]

Not sure to fully understand your request.
Assuming that columns "SKUS" et "Index" as useless and "Target hours" may difference between group.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci/KLy1QMFTSUTIyBhLGZkqxOsiiJtgEsao0NMAmit1UQ+yiZlgtMyTBBFPi3WBuhNUErLZhNdYMqwFGWIPBDCMcQZqNQISJKZooOCAxRC2MsYkamWATNTPFa24sAA==", BinaryEncoding.Base64), Compression.Deflate)),
             let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Group = _t, #"Hours of production run" = _t, #"Target hours" = _t]),
    cols = {Table.ColumnNames (Source)} & {{"Running Total", "Example Colum"}},
    #"Changed Type" = Table.TransformColumnTypes(Source,{{cols{0}{1}, Int64.Type}, {cols{0}{2}, Int64.Type}}),
    fx = (x, y)=> let x = List.Buffer(Table.ToColumns(x){1}),
                      y = List.Generate(
                          ()=> [ idx = 0,  a = x{0}, b = if x{0} < y then x{0} else "Not Eligible" ] ,
                          each [idx] <= List.Count(x)-1,
                          each [ idx = [idx] +1 , a = List.Sum({[a], x{[idx] + 1} }), b = if a < y then a else "Not Eligible"],
                          each [[a], [b]])
                  in List.Transform(y, each Record.ToList(_)),
    #"Grouped Rows" = Table.Group(#"Changed Type", cols{0}{0},
                      {{"x", each let target = List.Distinct(Table.Column(_, cols{0}{2})){0}
                      in Table.FromRows(
                      List.Transform(List.Zip({Table.ToRows(_) , fx(_,target)}),
                      List.Combine),  cols{0} & cols{1})
                      }})[x],
    Result = Table.Combine(#"Grouped Rows")
in
    Result

Regards

 

[alex78]

I just understood the initial request with JGordon11's answer - Code updated.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci/KLy1QMFTSUTIyBhLGZkqxOsiiJtgEsao0NMAmit1UQ+yiZlgtMyTBBFPi3WBuhNUErLZhNdYMqwFGWIPBDCMcQZqNMEwwwhqQIFELDE+ATcBqrhmGe1HMjQUA", 
             BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Group = _t, #"Hours of production run" = _t, #"Target hours" = _t]),
    cols = {Table.ColumnNames (Source)} & {{"Running Total", "Example Colum"}},
    #"Changed Type" = Table.TransformColumnTypes(Source,{{cols{0}{1}, Int64.Type}, {cols{0}{2}, Int64.Type}}),
    fx = (x, y)=> let x = List.Buffer(Table.ToColumns(x){1}), 
                      y = List.Generate(
                          ()=> [ idx = 0,  a =  x{0}, b = if a < y then x{idx} else "Not Eligible" ] ,
                          each [idx] <= List.Count(x)-1,
                          each [ idx = [idx] +1 , a = List.Sum({[a], x{[idx]+1} }), b = if a < y then x{idx} else if y - [a] < 0 then "Not Eligible" else  y - [a] ],
                          each [[a], [b]])
                  in List.Transform(y, each Record.ToList(_)),
    #"Grouped Rows" = Table.Group(#"Changed Type", cols{0}{0}, 
                      {{"x", each let target = List.Distinct(Table.Column(_, cols{0}{2})){0} 
                      in Table.FromRows(
                      List.Transform(List.Zip({Table.ToRows(_) , fx(_,target)}), 
                      List.Combine),  cols{0} & cols{1})
                      }})[x],
    Result = Table.Combine(#"Grouped Rows")
in
    Result

 

[sara1312]

@JGordon11 Thanks a lot for the support . Works perfectly .
But I am getting an expression error as the field name already exists. I tried to change the names but everytime I do , the column with new name and old one being retained .

 

[sara1312]

let
    Source = Excel.CurrentWorkbook(){[Name="YourTableName"]}[Content],
    Group = Table.Group(Source, {"Group"}, {{"All", each
        let
            tbl = Table.AddIndexColumn(_, "idx"),
            tbl1 = Table.AddColumn(tbl, "Example", each
                    if [Running Total] <= 36 then [Hours of production run] else
                    if [idx] = 0 then 36 else
                    if tbl[Running Total]{[idx] -1} < 36 then 36 - tbl[Running Total]{[idx] -1} else
                    "Not Eligble"
            )
        in 
            tbl1
    }}),
    Result = Table.RemoveColumns(Table.Combine(Group[All]), {"idx"})
in
    Result

 

[sara1312]

Hi, Instead of source to the excel work book , I would like to connect source to the previous step . Column naming is causing the issues

 

[sara1312]

Thank you so much , the refresh is not working to this column , before this step its working .Kindly help

 

[sara1312]

Thank you so much , the refresh is not working to this column , before this step its working .Kindly help

Hi could some one help on this please

 

[JGordon11]

Can you post your query?