Forum Discussion

SpreadsheetPete's avatar
SpreadsheetPete
Regular Visitor
4 years ago
Solved

Power Query Conditional Running Total with If Statement - Advanced Problem

Hi, This is an Advanced problem in M language with a very simple excel formula solution. And after many tryouts with many codes, I am kindly asking for Help, please.   I have managed to do the gr...
Need Help
  • jbwtp's avatar
    jbwtp
    4 years ago

    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

  • KT_Bsmart2gethe's avatar
    KT_Bsmart2gethe
    4 years ago

    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

KT_Bsmart2gethe's avatar
KT_Bsmart2gethe
Icon for Impactful Individual rankImpactful Individual
4 years ago

HI SpreadsheetPete ,

 

The challenge I can foresee now is the cyclic reference based on your excel formula between the Remaining Space and Running Total column. I will have an attempt with the recursive method later and let you know if any luck.

 

Regards

KT

SpreadsheetPete's avatar
SpreadsheetPete
Regular Visitor
4 years ago

Amazing Thank you

 

I did have some luck by adopting the below code and it works very closely yet it causes missed calculations after only some of the records. here is my latest code.

Also here is the updated Excel file demonstrating the output of the code and the error

 

the best code so far

let
Source = Excel.CurrentWorkbook(){[Name="SourceTable"]}[Content],
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, 
Int64.Type),
CorrectTypes = Table.TransformColumnTypes(#"Added Index",{{"Index", 
Int64.Type}, {"Filter", type text}, {"Volume", type number}}),

each List.Sum(Table.SelectRows(CorrectTypes, (Q) =>  Q[Filter] = 
[Filter] and Q[Index] <= [Index])[Volume]), type number),

Runing = Table.Group(CorrectTypes,"Filter",{"A", each let 
  A = Table.AddIndexColumn(_,"i")
  in Table.AddColumn(A,"R", each 
    List.Accumulate(Table.SelectRows(A, (a)=> a[i]<=[i])[Volume], 
[Running=0, Verifier = 1], 
// Here the challenge begins 
     (s,l)=> [Running = if s[Running]+l > 
(Number.RoundUp(s[Running]+[Volume]/[Cube])*[Cube]) then 
((Number.RoundUp(s[Running]/[Cube])*[Cube])-s[Running])+s[Running]+ 
[Volume] else s[Running]+l  , Verifier =Number.From(s[Running]+l 
<=Number.RoundUp(s[Running]/[Cube])*[Cube] )]  ))
}),
ExpandedR = Table.ExpandRecordColumn( 
Table.ExpandTableColumn(Runing, "A", {"Volume","Cube","i", "R"}), 
"R", {"Running", "Verifier"})
in

 

and here is the output

Code Output and formulas with desired outcome