Cumulative Sum using List.Accumulate with a twist

Hello

this detail you never told us

should the Bin # consider also empty space from the order before, or always start from 0 meaning if from order there is almost one empfty bin left, the new order is consideres to be placed into a new bin or can it use the empty space from order 1?

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

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

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

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

however Anonymous 

always ready to get some nice and tricky request by you 😄

Jimmy

Thanks Jimmy,

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

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? 

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

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? 

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? 

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? 

Nevermind! (Tell me if I'm wrong)

Simply adding  SKU to the last step includes it in the output! 

ExpandAllRows = Table.ExpandTableColumn(AddBin, "AllRows", {"SKU Qty", "Max Container", "Bin used","Final SKU"}, {"SKU Qty", "Max Container", "Bin used","Final SKU"})

Careful Jimmy the student is becoming the teacher! ha 

Thanks again Jimmy.

I am looking through the results now and noticed a few instances where the bin's are being overfilled? 

Hey

could you please share you real-life file.

What does overfill mean? that the function has calculated for example calculated 20 bins, when the order would need 30?

However, please share the file

Jimmy

 Sure. I highlighted 4 instances where the sum of the rows for each assigned bin exceeds the max bin limit, 

Not sure how to share excel files here so I uploaded to Google Drive:

Let me know if you cannot receive the file

https://drive.google.com/open?id=1W2LOCnsevvRTTDcuaRHQ2mcVo-Eyy6Rz 

hello Anonymous 

this is a .csv file. Not possible to highlight there something. But nevertheless... don't understand .. we never talked about max bin limit... the number we calculated was always how many bins you need to ship the order, considereing the sku count and the max sku count per bin... so we this framework, how would it possible that something gets exceeded?

Bye

Jimmy

My apologies, excel file is uploaded. to google drive.

I think the excel file will show you some good examples. What I called Max Bin Limit is the same as max SKU Count per bin.

For example if I have 3 rows within 1 Order and SKU count for each of these 3 rows is 10 and I have a Max Bin of 10 for this Order Then I would use 3 Bins and each row would be a new Bin Used number. Said in other words, the value in Max Bin column is the max allowed number SKU Counts that can fit into a single Bin. Hope that clears it up? 

Hello Anonymous 

are  you kidden me?

I mean, I'm loosing here time to check and understand (already used too much time) and at the end you are presenting wrong data. I mean when you ask a professional you pay deerly for all this. I did it for free in a very professional way to help....

I've downloaded your data, applied my funciton and it works perfectly fine as you have requested... a running sum to understand at wich point you need a new bin. The data you are represending in the file was not calculated with my function. see enclosed your version, and my version

your data

your raw data calculated with my function

the next thing is that you asked for a running sum, considering thi SKU count till that order line... but not considering that if a complete order line has to placed into a new bin once it's not possilbe to place it fully in the bin used before. This does mean you cant do a valuation like you did.

In Other words...

line 1 44000

line 2 5000

the function says 1 for line 1 and 2 for line 2. for a third line he would count 44000, 5000 and checks if for line 3 you would need a third bin this means that line 2 with bin 2 indicated are considere to be placed 500 in bin 1. So how can you sum all bin nr. 2 and check if this exceeds the max bin? Doesn't make any sense.

Sorry, but I'm off

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

Jimmy801 Obviously you are frustrated. There has been assumptions on both of our ends that has created this. I know there is logic built in the function that determines when the Bin used increments up by 1. I thought it would be a simple fix to change that logic from whatever it is now to increment up once the runningsum exceeds the Max Bin value. I thought that was obvious but as this post has proven nothing can be assumed. If you wish to not continue that is your perogative and I will respect it. Thank you for your time and efforts.