Forum Discussion

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 grouped running total in various methods yet could not twist any of the codes to achieve the below excel example.

The question is how to do a group running total that evaluates each step that if the next added line will be greater than the calculation of the passed variable and the sum up to this point and if it is greater than that calculation it adds the calculation to the sum and continues running total with the passed calculation.

Data is as simple as 3 columns: Filter - to group on, Volume - to sum up, and Cube - as a variable to perform the calculation.

Here are the basic example of excel files explaining the problem statement

in Line F10 you can see that formula triggers the if statement, that if the running total in F10(adding B10; B10 Greater than J9) is greater than Remaining space in J9 then it adds the J9 to the running total.

Where J9 is the calculation based on F9 and C9 etc...

This is the Example data with desire outcome

Here are the formulas

The best M code so far was by following the link below

https://www.mrexcel.com/board/threads/power-query-grouped-running-totals-with-a-maximum-condition-and-a-verifier.1186541/

Here is my code

`(values as list, grouping as list, cube as list) as list =>
let GRTList = List.Generate
( ()=> [ GRT = values{0}, i =0, cube =0],
each [i] < List.Count(values),
each try if grouping{[i]} = grouping{[i]+1}
and [GRT = [GRT] + values{[i]+1}, i=[i]+1]> Number.RoundUp([GRT]/ 
cube)*cube
then [GRT = [GRT] + values{[i]+1}, i=[i]+1]+(( 
Number.RoundUp([GRT]/ cube)*cube) -[GRT])
else [GRT = [GRT] + values{[i]+1}, i=[i]+1]

otherwise [i= [i]+1],
each[GRT]
)
in 
GRTList

And here is the callout function

#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 
 1, Int64.Type),

 BufferedTable5 = Table.Buffer(#"Added Index"),

 BufferedValues = List.Buffer(BufferedTable5[Volume]),
 BufferedFilter = List.Buffer(BufferedTable5[Filter for Running 
  total_Drop_Level]),
 BufferedCube = List.Buffer(BufferedTable5[Cube Per Posission]),
 RT = Table.FromColumns({BufferedTable5[Filter for Running 
 total_Drop_Level],BufferedTable5[Volume],
 
fxGroupedRunningTotal(BufferedValues,BufferedFilter,BufferedCube)},

Many thanks,

Need Help

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

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

9 Replies

jbwtp
Memorable Member
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
- SpreadsheetPete
  Regular Visitor
  4 years ago
  Hi John,
  
  Thank you for the help, your code does the job only to a certain point
  Where in 3rd step of calculation for Filter "A" adds to 0.14 the 0.05 it should add the remaining space from cube (0.15 - 0.14 = 0.1) to the current calculation 0.14+0.01 and then add the next line 0.05... the code reset and just start from 0.05 so it's missing the whole challenge I am facing 🙂 - I need Running total for entire Filter A but when remaining space is less than next cube to add it needs to absorb the remaining space to the running total.
  - SpreadsheetPete
    Regular Visitor
    4 years ago
    Hi John,
    
    After dipper analysis of your code, it might not give me exactly what I was looking for but actually, it produces better results than I could hope for, it gives me the correct position against the cube. meaning I can simplify my further steps by adopting a different perspective to my problem. Amazing WORK!!! Thank you Very Much!!!
    
    PS
    
    If you do have an idea how to twist the code to earlier comments that would be great help /learning, please. I am just so curious by now how I should have done it, hate to be defeated by a code 😉
    It will cause me a sleep less nights till I resolve this challenge.
    
    Kind regards
    
    Pete
KT_Bsmart2gethe
Impactful 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
  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
    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

Forum Discussion

Power Query Conditional Running Total with If Statement - Advanced Problem

9 Replies

Related content

IF statement and conditions

Dax IF/AND Statement with Null condition

if statement for conditional query in advanced editor

Advanced filters

Condition Statement