Transform table with maximum quantity of 100 per week

[Ramiroz]

I doubt that this one has a solution in Power Query, however I thought to share it on this forum, maybe!

I have a dataset of weekly orders, which I need to limit to a weekly total quantity of 100. Thus remainder should be carried over to the following week and so on, till the last week. If there is remainder you open a new week. Always respecting the upper limit per week of 100.

Dataset for original table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nc/NCcAwCIDRXTwH6k9MhwnZf42mxKJgCqUHL48P0d4B24EEBXgOIYwySZ3UKFSSK87VZpdVJIskEDvVXNErMS6qfmqkp7K7NFSnU/tUbXbR3+p+e1w=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Week = _t, Orders = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Week", Int64.Type}, {"Orders", Int64.Type}})
in
    #"Changed Type"

[Anonymous]

here some logic  of the "singular" mechanism that you have described :

let
    order={140,80,60,220},
    Source = List.Accumulate({1..9},[ord={List.Min({order{0},100})},res={List.Max({order{0}-100,0})}], (s,c)=> s&[ord=s[ord]&{List.Min({(order{c}? ?? 0)+s[res]{c-1},100})},res=s[res]&{List.Max({(order{c}? ?? 0)+s[res]{c-1}-100,0})}]),
    ord = Source[ord]
in
    ord

which transform

this list

in to this list

warning:

Using the list.accumulate function is not suitable for large tables

[Anonymous]

could you please give the result table you are after?

Another important aspect: should be the date column be sorted, before eatablish wich row has to be splitted?

[Ramiroz]

Hi @Anonymous 

I'm looking for the outcome table as such.

For the date column, not necessarily sorted, as long as the order and structure of the row/date/order-ID is maintained.

[Anonymous]

Is not completely clear to me the role of order id and what you mean

with this sentence: "as long as the order and structure of the row/date/order-ID is maintained".

If you don'thave sorted date and don't specify wich row you should move from actual week to the nex week (ween you have a surplus, as for week 2, you have many possibilities to chose. Which one is the rigth one?)

I'm afraid that the structure will not be maintained: wich id shoul have the yellow record of week 3?
Could you post a table with IDs too?

[danextian]

@Anonymous ,

My thoughts too. Which order id's quantity should be carried over and which order id in the next week this will be added to? There are no order id's in the sample provided.

[danextian]

Hi @Ramiroz ,

Please try this. The code below adds a row for current week's remainder which is then carried over as another row of data for the next week.

// Query1 (3)
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nc/NCcAwCIDRXTwH6k9MhwnZf42mxKJgCqUHL48P0d4B24EEBXgOIYwySZ3UKFSSK87VZpdVJIskEDvVXNErMS6qfmqkp7K7NFSnU/tUbXbR3+p+e1w=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Week = _t, Orders = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Week"}, {{"Total", each List.Sum(List.Transform([Orders], Number.From))}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Index",{{"Total", Int64.Type}}),
    #"Inserted Subtraction" = Table.AddColumn(#"Changed Type", "Subtraction", each [Total] - 100, type number),
    #"Added Custom" = Table.AddColumn(#"Inserted Subtraction", "Excess for Next Week", each - List.Sum(List.FirstN(#"Inserted Subtraction"[Subtraction],[Index])), Int64.Type),
    #"Added Custom3" = Table.AddColumn(#"Added Custom", "Remainder from Last Week", each try -#"Added Custom"[Excess for Next Week]{[Index]-2} otherwise null, Int64.Type),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom3",{"Week", "Excess for Next Week", "Remainder from Last Week"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Other Columns", {"Week"}, "Attribute", "Value"),
    #"Renamed Columns1" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Note"}, {"Value", "Orders"}}),
    #"Added Custom2" = Table.AddColumn(#"Renamed Columns1", "Date", each if [Note] = "Excess for Next Week" then "zzzz" else null, type text ),
    Custom1 = Table.Combine({Source, #"Added Custom2"}),
    #"Changed Type1" = Table.TransformColumnTypes(Custom1,{{"Orders", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Week", Order.Ascending}, {"Date", Order.Ascending}}),
    #"Replaced Value" = Table.ReplaceValue(#"Sorted Rows","zzzz",null,Replacer.ReplaceValue,{"Date"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Replaced Value",{"Date", "Note", "Week", "Orders"})
in
    #"Reordered Columns"

[Ramiroz]

Hi @danextian 

I tried the code, however I need:

1. Keep the structure and entries, as I have an order ID for each entry.
2. Just reduce or increase each entry, according to the rule of keeping it <100 per week
3. Add new entries, ie. week 6 and week7 to carry over the remainder of previous weeks and maintain the <100

[Jimmy801]

Hello @Ramiroz 

you need results per day, or just a leveld total number of orders per week?

BR

Jimmy

[Ramiroz]

Hi @Jimmy801,

Yes I need it per day, no aggregation. As I have an order ID for each line, and need to keep the same structure, just reduce or increase or add a new entry to adjust to be below the 100 per week limit.

[Jimmy801]

Hello @Ramiroz 

this is for sure really challenging. Need List.Generate to go through the table, check week and orders, hold old week and current total orders of week, create new lines when some conditions is true. For sure feasable but I can't invest too much time fore a solution 🙂

Maybe @ImkeF is faster in that 🙂

Sorry

Jimmy

[CNENFRNL]

@Ramiroz , try following M code pls

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nc/NCcAwCIDRXTwH6k9MhwnZf42mxKJgCqUHL48P0d4B24EEBXgOIYwySZ3UKFSSK87VZpdVJIskEDvVXNErMS6qfmqkp7K7NFSnU/tUbXbR3+p+e1w=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Week = _t, Orders = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Week"}, {{"Total", each List.Sum(List.Transform([Orders], Number.From))}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each List.Accumulate(
            {0..[Index]},
            0,
            (c, s) => Number.Mod(c + Number.Mod(#"Added Index"[Total]{s}, 100), 100)
        )
    ),
    #"Merged Queries" = Table.NestedJoin(Source, {"Week"}, #"Added Custom", {"Week"}, "Added Custom", JoinKind.LeftOuter),
    #"Expanded Added Custom" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom", {"Custom"}, {"Custom"})
in
    #"Expanded Added Custom"