I have a function that creates a running total. As it currently stands (without the red code) it will create a running sum for each group. I would like to expand on this somehow to include logic that says restart the running sum at the end of the current group or when the current number is greater than the number in the column Max Container. My attempt to do is in in red. What am I missing? (MyTable as table) as table =>letSource = Table.Buffer(MyTable),TableType = Value.Type(Table.AddColumn(Source, "Running Sum", each null, type number)),current = List.Skip(List.Accumulate(Source[SKU Cumulative Qty],{0},(current,state) => (current & {List.Last(current) + state}) or (current & {List.Last(Source[Max Container]) + state}) )),AddedRunningSum = Table.FromColumns(Table.ToColumns(Source)&{current},TableType)inAddedRunningSum

Anonymous

Not applicable

6 years ago

Solved

Cumulative Sum using List.Accumulate with a twist

I have a function that creates a running total. As it currently stands (without the red code) it will create a running sum for each group. I would like to expand on this somehow to include logic that says restart the running sum at the end of the current group or when the current number is greater than the number in the column Max Container. My attempt to do is in in red. What am I missing?

(MyTable as table) as table =>
let
Source = Table.Buffer(MyTable),
TableType = Value.Type(Table.AddColumn(Source, "Running Sum", each null, type number)),
current = List.Skip(List.Accumulate(Source[SKU Cumulative Qty],{0},(current,state) => (current & {List.Last(current) + state}) or (current & {List.Last(Source[Max Container]) + state}) )),
AddedRunningSum = Table.FromColumns(Table.ToColumns(Source)&{current},TableType)
in
AddedRunningSum

Need Help

Jimmy801

6 years ago

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

Jimmy801
6 years ago
: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

32 Replies

Jimmy801
Community Champion
6 years ago
hello
for sure everything is possible. But you have to describe it in a more clearer way... so the running sum has to be created by group (whaht group? Column names?)
It would be appreciated to have to workbook and an example of the desired result
Have a nice evening
Jimmy
- Anonymous
  Not applicable
  6 years ago
  Sorry!
  I'll try to paint a better picture! Maybe I am attacking it in a completely wrong manner! The problem I am solving for is to containerize orders into bins. In the example below there are 2 orders with 6 items each. Order 1 has a total qty of 30 and Order 2 has a total qty of 36. Order 1 can fit into bins with a max size of 12 and Order 2 can fit into bins with a max size of 20. I want to know how many bins order 1 will consume and how many order 2 will consume. I assumed I would need List.Accumulate to accomplish this.
  
  Example:
  
  Order SKU Count Max Bin Running Sum Expected Results Bin #
  1
  5 12 5 5 1
  1 5 12 10 10 1
  1 5 12 15 5 2
  1 5 12 20 10 2
  1 5 12 25 5 3
  1 5 12 30 10 3
  2 6 20 6 6 4
  2 6 20 12 12 4
  2 6 20 18 18 4
  2 6 20 24 6 5
  2 6 20 30 12 5
  2 6 20 36 18 5
  - artemus
    Microsoft Employee
    6 years ago
    First, do your accumulate as part of a group by operation on the Order Column, as this way you won't need to handle that column in your accumulate.
    
    Here is the simple, how many bins approach (not tested to see if this fully works):
    List.Accumulate(Table.ToRecords(_), [Running Sum = 0, Bin Count = 1], (current, next) => let #"Running Sum" = current[Running Sum] + next[SKU Count] in if #"Running Sum" > next[Max Bin] then [Running Sum = next[SKU Count], Bin Count = 1 + current[Bin Count]] else [Running Sum = #"Running Sum"] & current[[Bin Count]] )[Bin Count] //Remove this if you want to see how many are left in the last bin

Forum Discussion

Cumulative Sum using List.Accumulate with a twist

32 Replies

Related content

DataSource.Error List.Accumulate

Cumulative Total

List.Accumulate & Table.AddColum with Iteration Step Counter

Cumulative Subtraction using Criteria

Cumulative total more than current value using current filters

Order	SKU Count	Max Bin	Running Sum	Expected Results	Bin #
1	5	12	5	5	1
1	5	12	10	10	1
1	5	12	15	5	2
1	5	12	20	10	2
1	5	12	25	5	3
1	5	12	30	10	3
2	6	20	6	6	4
2	6	20	12	12	4
2	6	20	18	18	4
2	6	20	24	6	5
2	6	20	30	12	5
2	6	20	36	18	5