Power Query Conditional Running Total with If Statement - Advanced Problem

Hi SpreadsheetPete,

This is probably not the neatiest solution, but you can try something like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLQMzACU4amQMpQKVYHLm6KEDdCFjdHiBvjUG+CLG6JEDcFizth2GuGLI5kjjmyOJK9FmBxZwxxSyRxEyRzDA2QNSA5yBDo41gA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Filter = _t, Volume = _t, Cube = _t, Index = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Filter", type text}, {"Volume", type number}, {"Cube", type number}, {"Index", type text}}),
    Aggregate = List.Accumulate(Table.ToRecords(#"Changed Type"), {}, (a,n)=> a & {
Record.AddField(Record.AddField(n, "CubeFill", n[Volume] + (if List.IsEmpty(a) or List.Last(a)[CubeFill] + n[Volume] > n[Cube] or List.Last(a)[Filter] <> n[Filter] then 0 else List.Last(a)[CubeFill])),
"PositionCount", if List.IsEmpty(a) or List.Last(a)[Filter] <> n[Filter] then 1 else if List.Last(a)[CubeFill] + n[Volume] > n[Cube] then List.Last(a)[PositionCount]+Number.RoundUp(n[Volume]/n[Cube], 0)  else List.Last(a)[PositionCount])}),
    Output = Table.FromRecords(Aggregate)
in
    Output

Kind regards,

John

SpreadsheetPete ,

I corrected the code in fxCalc.

(A)=>
    let
        RunningTotal = Table.AddColumn(
                            A, 
                            "Running Total", 
                            each 
                                if [Volume] < 
                                        (Number.RoundUp(List.Sum(List.InsertRange(List.FirstN(A[Volume],[Index]),0,{0})) / [Cube], 0) * [Cube]) 
                                        - List.Sum(List.InsertRange(List.FirstN(A[Volume],[Index]),0,{0})) 
                                then @RunningTotal[Running Total]{[Index]-1} + [Volume] 
                                else List.Sum(List.FirstN(A[Volume],[Index]+1)) 
                                        + ((Number.RoundUp(List.Sum(List.InsertRange(List.FirstN(A[Volume],[Index]),0,{0})) / [Cube], 0) * [Cube])
                                        - List.Sum(List.InsertRange(List.FirstN(A[Volume],[Index]),0,{0})))
                        ),
        CubeFill = Table.AddColumn(
                        RunningTotal, 
                        "Cube Fill", 
                        each [Running Total]/[Cube]
                    ),
        PositionCount = Table.AddColumn(
                            CubeFill, 
                            "Position Count", 
                            each Number.RoundUp([Cube Fill],0)
                        ),
        RemainingSpace = Table.AddColumn(
                            PositionCount, 
                            "Remaining Space", 
                            each [Position Count] * [Cube] - [Running Total]
                        )
    in
        RemainingSpace

Main Query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLQMzACU4amSrE6cDFTLGLmRKqzRBZzwmKHExa9TljscMYpZmKKRR3C3lgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Filter = _t, Volume = _t, Cube = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Filter", type text}, {"Volume", type number}, {"Cube", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Filter"}, {{"Group", each fxCalc(Table.AddIndexColumn(_,"Index",0,1))}}),
    Combine = Table.Combine(#"Grouped Rows"[Group])
in
    Combine

Regards

KT