One query and one function.
Function, add this code to a blank query and rename it to fnBalances
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif](inp as table) as table =>
let
Source = inp,
amounts.List = List.Buffer(Source[Qty Due]),
Custom1 = Source,
grouping.Index = Table.AddIndexColumn(Custom1, "Index", 1, 1),
groupbalance.Addcol = Table.AddColumn(grouping.Index, "Balance", each List.Sum(List.FirstN(amounts.List, [Index])), type number),
index.Remove = Table.RemoveColumns(groupbalance.Addcol,{"Index"}),
layout.Finalised = Table.ReorderColumns(index.Remove,{"Doc Number", "Promised Date", "Part Ref", "Qty Due"})
in
layout.Finalised
The main query, I called it Running Totals By Part Ref
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
amounts.List = List.Buffer(Source[Qty Due]),
Custom1 = Source,
order.Indexcol = Table.AddIndexColumn(Custom1, "Original Order", 0, 1),
category.Group = Table.Group(order.Indexcol, {"Part Ref"}, {{"CategoryGroups", each _, type table [Doc Number=text, Promised Date=date, Part Ref=text, Qty Due=number]}}),
balances.AddToTable = Table.AddColumn(category.Group, "Custom", each fnBalances([CategoryGroups])),
balances.Expand = Table.ExpandTableColumn(balances.AddToTable, "Custom", {"Doc Number", "Promised Date", "Qty Due", "Original Order", "Balance"}, {"Doc Number", "Promised Date", "Qty Due", "Original Order", "Balance"}),
headers.Rename = Table.RenameColumns(balances.Expand,{{"Balance", "Category Balance"}}),
original.Resort = Table.Sort(headers.Rename,{{"Original Order", Order.Ascending}}),
tempcols.Remove = Table.RemoveColumns(original.Resort,{"CategoryGroups", "Original Order"}),
layout.Finalised = Table.ReorderColumns(tempcols.Remove,{"Doc Number", "Part Ref", "Promised Date", "Qty Due", "Category Balance"})
in
layout.Finalised[/FONT][SUB][SUP]
[/SUP][/SUB][/FONT]
