Forum Discussion
Generate Moving Closing Balance Monthly in m-Query
let ob_start = 100, Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], s = ((rows) => List.Generate( () => [i = 0, r = rows{0}, ob = ob_start, av_stock = ob + r{1}, cl_bal = av_stock + r{2}], (x) => x[i] < List.Count(rows), (x) => [i = x[i] + 1, r = rows{i}, ob = x[cl_bal], av_stock = ob + r{1}, cl_bal = av_stock + r{2}], (x) => x[r] & {x[i], x[ob], x[av_stock], x[cl_bal]} ))(Table.ToList(Source, each _)), z = Table.FromList(s, each _, Table.ColumnNames(Source) & {"Index", "Opening Balance", "Available Stock", "Closing Balance"}) in z
Hi iktsharma ,
Thank you for choosing the Microsoft fabric Community Forum!
Thank you AlienSx for the helpful response.
Upon reviewing the provided information, below steps might assist you in the getting the expected output.
- After loading the data,In Power Query Editor, go to the Home tab.
- Click on Advanced Editor.
- Replace it with the following M Query :
let
Source = Table.FromRecords({
[FileDate=#date(2024,1,1), Index=0, Quantity=10, Consumption=-20],
[FileDate=#date(2024,2,1), Index=1, Quantity=20, Consumption=-25],
[FileDate=#date(2024,3,1), Index=2, Quantity=15, Consumption=-17],
[FileDate=#date(2024,4,1), Index=3, Quantity=10, Consumption=-12]
}),
// Sorting by Index for correct calculation
SortedTable = Table.Sort(Source, {{"Index", Order.Ascending}}),
// Opening Balance
OpeningBalanceInitial = 100,
// Convert table into a list
TableList = Table.ToRecords(SortedTable),
// List.Accumulate to calculate balances iteratively
ComputedList = List.Accumulate(
TableList,
{OpeningBalanceInitial, {}},
(state, current) =>
let
PreviousClosingBalance = state{0},
OpeningBalance = PreviousClosingBalance,
AvailableStock = OpeningBalance + current[Quantity],
ClosingBalance = AvailableStock + current[Consumption],
NewRecord = [
FileDate = current[FileDate],
Index = current[Index],
OpeningBalance = OpeningBalance,
Quantity = current[Quantity],
Consumption = current[Consumption],
AvailableStock = AvailableStock,
ClosingBalance = ClosingBalance
]
in
{ClosingBalance, state{1} & {NewRecord}}
){1}, // Extract computed records
// Convert the computed list back to table
ResultTable = Table.FromRecords(ComputedList)
in
ResultTable
Refer the attached file and the screenshot for detailed understanding.
If this post helps, please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.
Regards,
Pallavi.