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

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

KT_Bsmart2gethe's avatar
KT_Bsmart2gethe
Impactful Individual
4 years ago

Hi SpreadsheetPete ,

 

Please see below code (There are still a slight variance in the calculation which I will look into it tomorrow.)

 

Working 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

 

 

 

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 List.Sum(List.FirstN(A[Volume],[Index]+1))
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

 

Regards

KT

  • KT_Bsmart2gethe's avatar
    KT_Bsmart2gethe
    Impactful Individual
    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

    • SpreadsheetPete's avatar
      SpreadsheetPete
      Regular Visitor
      4 years ago

      KT

      THANK YOU VERY MUCH!!!

      You Are The Winner Here as your code does exactly what I was struggling with for over a week now.

      This is absolutely amazing! I cannot thank you enough

      Beautiful work!

       

      John,

      Thank you to you too as your code is also very good and very useful and it has provided me a different perspective To my challenge so also very grateful for your work.

       

      You are both lifesavers!

       

      Kind Regards

       

      Pete