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

Anonymous

Not applicable

6 years ago

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

Community Champion

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

Community Champion

6 years ago

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

Jimmy801
Community Champion
6 years ago
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
Jimmy801
Community Champion
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
however Anonymous
always ready to get some nice and tricky request by you 😄
Jimmy
Anonymous
Not applicable
6 years ago
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?
Anonymous
Not applicable
6 years ago
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?
Anonymous
Not applicable
6 years ago
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?
Anonymous
Not applicable
6 years ago
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
Anonymous
Not applicable
6 years ago
Thanks again Jimmy.

I am looking through the results now and noticed a few instances where the bin's are being overfilled?
Jimmy801
Community Champion
6 years ago
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
Anonymous
Not applicable
6 years ago
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
Jimmy801
Community Champion
6 years ago
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
Anonymous
Not applicable
6 years ago
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?
Jimmy801
Community Champion
6 years ago
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
Anonymous
Not applicable
6 years ago
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.

Forum Discussion

Cumulative Sum using List.Accumulate with a twist

Related content

Cumulative Total

DataSource.Error List.Accumulate

List.Accumulate & Table.AddColum with Iteration Step Counter

Creating Cumulative Graphs

Cumulative Subtraction using Criteria