Forum Discussion
Solved
Generate Moving Closing Balance Monthly in m-Query
Hi, I'm looking for a solution to implement the below table in m-Query in Power BI: FileDate Index Opening Balance Quantity Consumption AvailableStock Closing Balance 01-Jan-24 0 ...
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
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
