Cumulative Sum using List.Accumulate with a twist

hey

and what was the BIN# in your post?

Now this does simplify things 🙂

This will be my last post on this thread 😉

3 beers - some kudoes and 3 solutions as min, allright Anonymous ? 😉

(tTable as table) as table =>
let
    Group = Table.Group(tTable, {"Order"}, {{"AllRows", each _}}),
    fnRowIndex = (tbl as table, sumcolumn as text, rowindex as number) =>
        let
            #"Removed Other Columns" = Table.SelectColumns(tbl,{sumcolumn, "Index"}),
            #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each [Index] <= rowindex),
            #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{sumcolumn, "Temp"}}),
            #"Grouped Rows" = Table.Group(#"Renamed Columns", {}, {{"RunningTotal", each List.Sum([Temp]), type number}}),
            RunningTotal = Record.Field(#"Grouped Rows"{0},"RunningTotal")
        in
            RunningTotal,
      
    AddIndex = Table.TransformColumns
        (
            Group,
            {{"AllRows", (transform) => Table.AddIndexColumn(transform, "Index", 1)}}
        ),
    AddRunSum = Table.TransformColumns
        (
            AddIndex,
            {{"AllRows", (transfom) => Table.AddColumn
                (
                    transfom,
                    "Run",
                    (add)=> fnRowIndex(transfom, "SKU Count", add[Index]) 
                )}}
        ),
    AddBin = Table.TransformColumns
        (
            AddRunSum,
            {{"AllRows", (transform) => Table.AddColumn
                (
                    transform,
                    "Bin used",
                    (add)=> Number.RoundUp(add[Run]/add[Max Bin])
                )}}
        ),
    ExpandAllRows = Table.ExpandTableColumn(AddBin, "AllRows", {"SKU Count", "Max Bin", "Bin used"}, {"SKU Count", "Max Bin", "Bin used"})
    /*ExtractBin = Table.AddColumn
        (
            AddBin,
            "Bin used",
            each [AllRows][Bin used]
        ),
    #"Hinzugefügter Index" = Table.AddIndexColumn(ExtractBin, "Index", 1, 1),
    AddMax =     Table.AddColumn
        (
             #"Hinzugefügter Index",
            "Max List",
            each List.Max([Bin used])
        ),
    NumberMaxList = Table.TransformColumnTypes(AddMax,{{"Max List", Int64.Type}}),
    AddChangedUsedBin = Table.AddColumn
        (
             NumberMaxList,
    /        "New Bin used",
            (add) => List.Transform(add[Bin used], (listtransform) => if add[Index] = 1 then listtransform else listtransform + List.Sum
                (
                    Table.SelectRows
                        (
                            NumberMaxList, (select)=> select[Index]< add[Index]
                        )[Max List]
                )
                )

        ),
    AddNewColumn = Table.AddColumn
        (
            NumberMaxList,
            "Final table",
            (add)=> Table.Join(add[AllRows], "Index", Table.AddIndexColumn(Table.FromList(add[New Bin used],Splitter.SplitByNothing(),{"Bin Used"},ExtraValues.Error),"Index1",1),"Index1")
        ),
    DeletedRows = Table.RemoveColumns(AddNewColumn,{"AllRows", "Bin used", "Index", "Max List", "New Bin used"}),
    Expand = Table.ExpandTableColumn(DeletedRows, "Final table", {"SKU Count", "Max Bin", "Bin Used"}, {"SKU Count", "Max Bin", "Bin Used"})
    */
in
    ExpandAllRows

have a nice evening

Jimmy

:D:D

I don't know if I should laugh o cry

I don't know how you are applying my function.. you have to pass your whole table into the fucntion and using the result not adding columns to multply it 😄

Bye Jimmy