Cumulative Sum using List.Accumulate with a twist

[Anonymous]

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

[Jimmy801 · Accepted solution]

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]

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]

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

[Jimmy801]

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]

Thank you! I'm not very good with python. How would I modify this to accept my query data? 

[Jimmy801]

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

[Anonymous]

Correct, but the sample I provided is just a sample. It is not the full length table that I am transforming. The input data needs to be dynamic. your query is static by nature. 

[Jimmy801]

Hello

I provided an example that can  be tested by everyone. So if you want to transfer it to a function cut away the table-part and add it as parameter like this

(YourTable as table) as table =>
let

    GroupedTable = Table.Group
        (
        YourTable, 
        {"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

[Anonymous]

Sorry for the confusion! But in my example the input data only consists of the Order, SKU Count, and Max Bin. The Bin # is the only real output that matters, the running sum and expected results were built to show the logic as to how the Bin # is asssigned to each order record. Thanks! 

[Jimmy801]

hey

so this took me quite a while to figure out. But finally i got it.

Function has to be feed with table with 3 column as stated in your post. Adds "Bin used" as requested

(tTable as table) as table =>
let
    Changetype = Table.TransformColumnTypes(tTable,{{"Order", Int64.Type}, {"SKU Count", Int64.Type}, {"Max Bin", Int64.Type}}),
    Group = Table.Group(Changetype, {"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])
                )}}
        ),
    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 + Table.SelectRows(NumberMaxList, (select)=> select[Index]= add[Index]-1)[Max List]{0})

        ),
    AddNewColumn = Table.AddColumn
        (
            AddChangedUsedBin,
            "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
    Expand

Have fun

Jimmy

[Anonymous]

Thanks Jimmy,

I am getting errors, Orders aren't always integers, can this be switched to text without any reprecussions? 

[Jimmy801]

Hello

okay, also this was never stated. Found also another error. But this now should do it.

You owe me a favour, you know? Here the code

(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])
                )}}
        ),
    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
        (
            AddChangedUsedBin,
            "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
    Expand

Have fun

Jimmy

[Anonymous]

I definitely owe you a beer or 2! Thank you so much! The results are very close to being right! the difference in complexity between the example data and my actual data is quite large, forgive me for providing erroneous info. 

That being said, I may have mispoke when I said do not restart every Order. Can you adjust the code to restart every Order? 

[Jimmy801]

Hello
Yes, it restarts on every order, as if shipping... meaning every order needs its own shipping.
Don't tell me that it doesn't work... 😉
Spent too much time on it. But when you are speaking about beers... you have one chance left to change the code. But in case.. give me a real example with the desired output
Jimmy

[Anonymous]

Thanks! Here is what I am seeing with my data. Bins Used is your results, Expected Bin is what I mean by restarting with each new Order. It does not appear it is restarting currently. I color coded each Order to visualize the different orders and show the difference between Bin Used and Expected Bin

[Jimmy801 · Accepted solution]

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

[Anonymous]

Thank you Jimmy! 

The solution works splendidly! Thank you Thank you! It does take quite a while to run when running for hundreds/thousands of records. Do you have any quick tips on how to get the solution to run through the records a little faster? 

[Jimmy801]

hey

give it a try to change this part

    Group = Table.Group(tTable, {"Order"}, {{"AllRows", each _}}),
    fnRowIndex = (tbl as table, sumcolumn as text, rowindex as number) =>

to this

    BufferedTable = Table.Buffer(tTable),
    Group = Table.Group(BufferedTable , {"Order"}, {{"AllRows", each _}}),
    fnRowIndex = (tbl as table, sumcolumn as text, rowindex as number) =>

and let me know

Jimmy

[Anonymous]

I will add it. But I think I found the main culprit to the run time issue. Before the function I have 816 records, after the function I have over 665,000! I noticed in the last function provided you commented out a Delete Rows step, but there is no Delete Rows step in the uncommented section. Could that be causing the massive increase in rows? 

[Jimmy801]

Hello

I don't know what you mean.

I mean I tested it right now with a dataset from 100k. It took less then 15 seconds loading into Power BI. I inputed 100k into the function and i got back excactly the same. So no issue with the function.

Bye

Jimmy

[Anonymous]

I see what is happening. When I run the function using the base query with the input fields (816 rows) it returns the results 816 times, so when I expand the table it results in 865k rows (816x816).

Last question and I promise I will leave you alone! 

If I wanted to pull decriptive columns through the function how would I do that? For example. each line in the input data set represents a SKU #. How could I see SKU # included in the function output?