Transform table with maximum quantity of 100 per week

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"