Forum Discussion

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...

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

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

Jimmy801

Community Champion

6 years ago

Hello

didn't get the final result you were looking for. In my solution you can find a new column "Bin used for order" that indicated on every row!! the bins used for that order. I would prefer to have a aggregated view on order level

let
    Quelle = Table.FromRows
        (
            Json.Document
                (
                    Binary.Decompress
                        (
                            Binary.FromText
                                (
                                    "dc7BDcAgCAXQXTh7KKDEXYz7r1GrwEHoASK+fMIYgFCgrULSxx5glovw8ZagBSkaeTBDC3I09uDB709soWjVSOci+sFuLUGqOrVo7KszFNu6cL4=", BinaryEncoding.Base64
                                ), 
                            Compression.Deflate
                        )
                ), 
                let _t = ((type text) meta [Serialized.Text = true]) in type table [Order = _t, #"SKU Count" = _t, #"Max Bin" = _t, #"Running Sum" = _t, #"Expected Results" = _t, #"Bin #" = _t]),
    ChangedType = Table.TransformColumnTypes
        (
            Quelle,
            {{"Order", Int64.Type}, 
            {"SKU Count", Int64.Type},
            {"Max Bin", Int64.Type}, 
            {"Running Sum", Int64.Type}, 
            {"Expected Results", Int64.Type}, 
            {"Bin #", Int64.Type}}
        ),
    GroupedTable = Table.Group
        (
        ChangedType, 
        {"Order", "Max Bin"}, 
        {{"AllRows", each _, type table [Order=number, SKU Count=number, Max Bin=number, Running Sum=number, Expected Results=number, #"Bin #"=number]}}
        ),
    BinUsed = Table.AddColumn(GroupedTable, "Bin used for order", (newColumn)=> Number.Round(List.Sum(newColumn[AllRows][SKU Count])/newColumn[Max Bin],0, RoundingMode.Up)),
    ExpandAllRows = Table.ExpandTableColumn(BinUsed, "AllRows", {"SKU Count", "Running Sum", "Expected Results", "Bin #"}, {"SKU Count", "Running Sum", "Expected Results", "Bin #"})


in
    ExpandAllRows

have fun

Jimmy

Anonymous
Not applicable
6 years ago
Thank you! I'm not very good with python. How would I modify this to accept my query data?
- Jimmy801
  Community Champion
  6 years ago
  Hello
  my code is simple M-Code
  copy it all an paste it in a blank query. Then you will see the result
  have fun
  Jimmy

Forum Discussion

Cumulative Sum using List.Accumulate with a twist

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