stock at the end of period by categories with Power Query

[HerGP]

Hello, I need help with a calculation in Power Query M for an inventory file. My file has the following structure:

Column structure:

• Columns A to F: Represent the original exported report.
• Columns I to N: Have the same structure but include an additional row labeled "EOM," calculated dynamically.

Calculation details:

• "EOM" should be calculated by summing the elements of the "ORDER TYPE" for a period (original columns) for the corresponding "ITEM."
• This "EOM" is inserted as a new row with the calculated value and a date in the "SUGG DUE DATE" column corresponding to the end of the period.
• The "EOM" row for a period is used as the base to perform the same calculation for the subsequent period, and so on (highlighted in orange).

My goal:Create the M code that:

1. Inserts the "EOM" rows for each period.
2. Recalculates the information iteratively for subsequent periods.
3. Source = Excel.Workbook(File.Contents("\\tjpavcv09\Finance$\HG55813\My Documents\My Documents\Impro\INVENTORY FORECAST\20250103 OES Weekly new rev.xlsx"), null, true),
   FNDWRR_Sheet = Source{[Item="FNDWRR",Kind="Sheet"]}[Data],
   #"Removed Blank Rows" = Table.SelectRows(FNDWRR_Sheet, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
   #"Changed Type" = Table.TransformColumnTypes(#"Removed Blank Rows",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type any}, {"Column5", type text}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type text}, {"Column15", type any}, {"Column16", type any}, {"Column17", type text}, {"Column18", type any}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", type text}, {"Column23", type text}, {"Column24", type text}, {"Column25", type text}, {"Column26", type text}, {"Column27", type text}, {"Column28", type text}, {"Column29", type text}, {"Column30", type text}, {"Column31", type text}, {"Column32", type text}}),
   #"Removed Top Rows" = Table.Skip(#"Changed Type",2),
   #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
   #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"ORGANIZATION", type text}, {"ITEM", type text}, {"ORDER TYPE", type text}, {"QUANTITY", type number}, {"SUGG DUE DATE", type text}, {"CATEGORY CODE", type text}, {"BUYER", type text}, {"PREPROCESSING LEAD TIME", Int64.Type}, {"POSTPROCESSING LEAD TIME", Int64.Type}, {"PROCESSING LEAD TIME", Int64.Type}, {"TOTAL LEAD TIME", Int64.Type}, {"FIXED LOT MULTIPLIER", type number}, {"VENDOR NAME", type text}, {"VENDOR SITE CODE", type text}, {"VENDOR SITE CODE ALT", type text}, {"STANDARD COST", type number}, {"DESCRIPTION", type text}, {"MINIMUM ORDER QUANTITY", type number}, {"WEIGHT UOM", type text}, {"UNIT WEIGHT", type text}, {"VOLUME UOM", type text}, {"UNIT VOLUME", type text}, {"DIMENSION UOM", type text}, {"UNIT LENGTH", type text}, {"UNIT WIDTH", type text}, {"UNIT HEIGHT", type text}, {"CONTAINER", type text}, {"VEHICLE", type text}, {"CONTAINER TYPE", type text}, {"INTERNAL VOLUME", type text}, {"MAXIMUM LOAD WEIGHT", type text}, {"MINIMUM FILL PERCENT", type text}}),
   #"Removed Errors" = Table.RemoveRowsWithErrors(#"Changed Type1", {"VENDOR NAME"}),
   #"Filtered Rows NO NULL" = Table.SelectRows(#"Removed Errors", each [ITEM] <> null),
   #"Added Custom AMOUNT" = Table.AddColumn(#"Filtered Rows NO NULL", "AMOUNT", each [QUANTITY]*[STANDARD COST]),
   #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom AMOUNT",{{"AMOUNT", type number}}),
   #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type2", {"ORGANIZATION", "ITEM", "ORDER TYPE", "SUGG DUE DATE", "CATEGORY CODE", "BUYER", "PREPROCESSING LEAD TIME", "POSTPROCESSING LEAD TIME", "PROCESSING LEAD TIME", "TOTAL LEAD TIME", "FIXED LOT MULTIPLIER", "VENDOR NAME", "VENDOR SITE CODE", "VENDOR SITE CODE ALT", "STANDARD COST", "DESCRIPTION", "MINIMUM ORDER QUANTITY", "WEIGHT UOM", "UNIT WEIGHT", "VOLUME UOM", "UNIT VOLUME", "DIMENSION UOM", "UNIT LENGTH", "UNIT WIDTH", "UNIT HEIGHT", "CONTAINER", "VEHICLE", "CONTAINER TYPE", "INTERNAL VOLUME", "MAXIMUM LOAD WEIGHT", "MINIMUM FILL PERCENT"}, "Attribute", "Value"),
   #"Pivoted Column" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[#"ORDER TYPE"]), "ORDER TYPE", "Value", List.Sum),
   #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"ORGANIZATION", "ITEM", "SUGG DUE DATE", "CATEGORY CODE", "BUYER", "PREPROCESSING LEAD TIME", "POSTPROCESSING LEAD TIME", "PROCESSING LEAD TIME", "TOTAL LEAD TIME", "FIXED LOT MULTIPLIER", "VENDOR NAME", "VENDOR SITE CODE", "VENDOR SITE CODE ALT", "STANDARD COST", "DESCRIPTION", "MINIMUM ORDER QUANTITY", "WEIGHT UOM", "UNIT WEIGHT", "VOLUME UOM", "UNIT VOLUME", "DIMENSION UOM", "UNIT LENGTH", "UNIT WIDTH", "UNIT HEIGHT", "CONTAINER", "VEHICLE", "CONTAINER TYPE", "INTERNAL VOLUME", "MAXIMUM LOAD WEIGHT", "MINIMUM FILL PERCENT", "Attribute", "On Hand", "Purchase order", "Intransit shipment", "Planned order", "Work order demand", "Sales order MDS"})
   in
   #"Reordered Columns"[/CODE]