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

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

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

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_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

Forum Discussion

Power Query Conditional Running Total with If Statement - Advanced Problem

Related content

Dax IF/AND Statement with Null condition

IF statement and conditions

if statement for conditional query in advanced editor

Advanced filters

Condition Statement