Multirow calculation with judgements and loop nesting

Hi Anonymous 

I got the loop working in Power Query now. You can download the Sample.xlsx as the input file and see the M Code in the attached Power BI file SampleLoop.pbix in same folder. Hope this helps.

SampleLoop

Here's the full M Code. You have to enter the intial OpeningValue as a parameter and let it run.

let
Source = Excel.Workbook(File.Contents("C:\Users\aliom\OneDrive\SampleLoop\Sample.xlsx"), null, true),
FirstOpeningValue = 10,
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Quarter", type text}, {"Value01", Int64.Type}, {"Value02", Int64.Type}, {"Value03", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),

// Custom function to perform the calculations
CustomCalculation = (Index as number, PrevEndingValue as number) as record =>
let
CurrentRow = #"Added Index"{Index},
OpeningValue = if Index = 0 then FirstOpeningValue else PrevEndingValue,
Value01 = CurrentRow[Value01],
Value02 = CurrentRow[Value02],
Value03 = CurrentRow[Value03],
Value04 = if (OpeningValue + Value01) > (Value02 + Value03) then List.Min({OpeningValue, Value02 + Value03}) else 0,
EndingValue = OpeningValue + Value01 + Value02 + Value03 + Value04
in
[Index=Index, OpeningValue=OpeningValue, Value04=Value04, EndingValue=EndingValue],

// Generate the list of calculations
Calculations = List.Generate(
()=> [Index=0, PrevEndingValue=FirstOpeningValue, Calc=CustomCalculation(0, FirstOpeningValue)],
each [Index] < Table.RowCount(#"Added Index")+1,
each [Index=[Index]+1, PrevEndingValue=[Calc][EndingValue], Calc=CustomCalculation([Index], [Calc][EndingValue])],
each [Calc]
),

// Convert the list to a table and expand the record columns
CalculatedTable = Table.FromList(Calculations, Splitter.SplitByNothing(), {"Calc"}),
ExpandedTable = Table.ExpandRecordColumn(CalculatedTable, "Calc", {"Index", "OpeningValue", "Value04", "EndingValue"}),
#"Removed Duplicates" = Table.Distinct(ExpandedTable, {"Index"}),
// Join with the original table
FinalTable = Table.NestedJoin(#"Added Index", {"Index"}, #"Removed Duplicates", {"Index"}, "NewColumns", JoinKind.LeftOuter),
#"Expanded NewColumns" = Table.ExpandTableColumn(FinalTable, "NewColumns", {"Index", "OpeningValue", "Value04", "EndingValue"}, {"NewColumns.Index", "NewColumns.OpeningValue", "NewColumns.Value04", "NewColumns.EndingValue"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded NewColumns",{"OpeningValue", "Value04", "EndingValue", "NewColumns.Index"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Quarter", "Index", "NewColumns.OpeningValue", "Value01", "Value02", "Value03", "NewColumns.Value04", "NewColumns.EndingValue"}),
#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"NewColumns.OpeningValue", "OpeningValue"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns",null,0,Replacer.ReplaceValue,{"OpeningValue"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,0,Replacer.ReplaceValue,{"NewColumns.Value04"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value1",{{"NewColumns.Value04", Int64.Type}}),
#"Replaced Value2" = Table.ReplaceValue(#"Changed Type1",null,0,Replacer.ReplaceValue,{"NewColumns.EndingValue"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value2",{{"NewColumns.EndingValue", Int64.Type}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type2",{{"NewColumns.Value04", "Value04"}, {"NewColumns.EndingValue", "EndingValue"}})
in
#"Renamed Columns1"