Forum Discussion
Transform table with maximum quantity of 100 per week
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"
11 Replies
CNENFRNLCommunity Champion
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"
Jimmy801Hello 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
- danextian
Super User
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"Hi danextian
I tried the code, however I need:
- Keep the structure and entries, as I have an order ID for each entry.
- Just reduce or increase each entry, according to the rule of keeping it <100 per week
- Add new entries, ie. week 6 and week7 to carry over the remainder of previous weeks and maintain the <100
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.
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?
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 ordwhich transform
this list
in to this list
warning:
Using the list.accumulate function is not suitable for large tables
