Power Query Conditional Running Total with If Statement - Advanced Problem

[SpreadsheetPete]

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-an...

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,

[jbwtp · Accepted solution]

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]

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]

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]

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 · Accepted solution]

@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]

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 

[jbwtp · Accepted solution]

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]

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]

One hint of observation to note with this code, it heavily depends on the filter being sorted in group order before the index applies. if the filter is not sorted like AAABBBCCC and left as ABAABBACA it will just give possession 1 to every step...so wonder if we should apply the index to each grouped list.. rather than to the table...

[SpreadsheetPete]

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