Spreadsheet: 886713d1736825901-power-query-onhand-calculation-power-query-onhand-calculation
Sheet: M Code

1 | M code to create sequence column in original data:
 | let
 | Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 | #"Duplicated Column" = Table.DuplicateColumn(Source, "ITEM", "ITEM - Copy"),
 | #"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "SUGG DUE DATE", "SUGG DUE DATE - Copy"),
 | #"Added Index" = Table.AddIndexColumn(#"Duplicated Column1", "Index", 1, 1, Int64.Type),
 | #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Added Index", {{"SUGG DUE DATE - Copy", type text}, {"Index", type text}}, "en-MY"),{"ITEM - Copy", "SUGG DUE DATE - Copy", "Index"},Combiner.CombineTextByDelimiter("/", [NAME].None),"Sequence"),
 | #"Sorted Rows" = Table.Sort(#"Merged Columns",{{"Sequence", [NAME].Ascending}})
 | in
 | #"Sorted Rows"
2 | M code to create group, add running total and add sequence:
 | let
 | Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 | #"Grouped Rows" = Table.Group(Source, {"ORGANIZATION", "ITEM", "COST", "SUGG DUE DATE"}, {{"QUANTITY", each List.Sum([QUANTITY]), type nullable number}}),
 | #"Added Custom" = Table.AddColumn(#"Grouped Rows", "ORDER TYPE", each "EOM"),
 | #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"ORGANIZATION", "ITEM", "ORDER TYPE", "QUANTITY", "COST", "SUGG DUE DATE"}),
 | #"Grouped Rows1" = Table.Group(#"Reordered Columns", {"ITEM"}, {{"All", each _, type table [ORGANIZATION=text, ITEM=text, ORDER TYPE=text, QUANTITY=nullable number, COST=number, SUGG DUE DATE=text]}}),
 | #"Invoked Custom Function" = Table.AddColumn(#"Grouped Rows1", "RunningTotal", each RunningTotal([All])),
 | #"Expanded RunningTotal" = Table.ExpandTableColumn(#"Invoked Custom Function", "RunningTotal", {"COST", "ORDER TYPE", "ORGANIZATION", "QUANTITY.1", "SUGG DUE DATE"}, {"COST", "ORDER TYPE", "ORGANIZATION", "QUANTITY.1", "SUGG DUE DATE"}),
 | #"Removed Columns" = Table.RemoveColumns(#"Expanded RunningTotal",{"All"}),
 | #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"QUANTITY.1", "QUANTITY"}}),
 | #"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns",{"ORGANIZATION", "ITEM", "ORDER TYPE", "QUANTITY", "COST", "SUGG DUE DATE"}),
 | #"Duplicated Column" = Table.DuplicateColumn(#"Reordered Columns1", "ITEM", "ITEM - Copy"),
 | #"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "SUGG DUE DATE", "SUGG DUE DATE - Copy"),
 | #"Added Custom1" = Table.AddColumn(#"Duplicated Column1", "Custom", each 99999),
 | #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Added Custom1", {{"Custom", type text}}, "en-MY"),{"ITEM - Copy", "SUGG DUE DATE - Copy", "Custom"},Combiner.CombineTextByDelimiter("/", [NAME].None),"Sequence")
 | in
 | #"Merged Columns"
3 | M code to invoke a custom running total function:
 | (RunningTotal) =>
 | let
 | //    Source = Table1,
 | //    A = Source{[ITEM="A"]}[All],
 | #"Added Index" = Table.AddIndexColumn(RunningTotal, "Index", 1, 1, Int64.Type),
 | #"Added Custom" = Table.AddColumn(#"Added Index", "QUANTITY.1", each List.Sum(List.FirstN(#"Added Index"[QUANTITY],[Index])))
 | in
 | #"Added Custom"
4 | M code to merge original data with EOM data:
 | let
 | Source = Table.Combine({Table1, Group}),
 | #"Sorted Rows" = Table.Sort(Source,{{"Sequence", [NAME].Ascending}}),
 | #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Sequence"})
 | in
 | #"Removed Columns"