Running total with conditions based on another column

[rhys1987]

Hi, below is a basic sample of the data...

I want the number column to change to 2 when running total hits 20 but it can't if the code is not new.

I prefer to do it in power query, i have tried numerous methods and can't seem to get it to talk to the code column and put the condition in to only update the nuber if the code is new.

Table shows that it only changes to 2 when the code changes even if the running total is passed 20.

Thanks!

[rhys1987]

Sorry i didn't explain it well.

Basically it needs to be that the last column/count cant increase if code is the same only when it changes.

So it might hit the running total of 20 which is needed to count up by 1 BUT the code is still the same so it needs for that to change.

[Jimmy801]

Hello @rhys1987 

sorry I can't follow you. 

Could you please post your dataset and what your exactly output is

BR

Jimmy

[JW_van_Holst]

You might want to extend your test set. To use this code and second column Table (2) is needed: distinct codes and een column "Seq" with an index starting with 1. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjQ3MbcwUtJRMgNiQzCO1UESNgQRRljELYCEMaa4kQmQMMEUNzYAEqYIcTMzUwOQkLEZ1G4jVHETkCZziHgsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Code = _t, RT = _t, Index = _t, #"Desired Result" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", Int64.Type}, {"RT", Int64.Type}, {"Index", Int64.Type}, {"Desired Result", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "PreviousCode", each if [Index] = 1 then [Code] else  #"Changed Type"{[Index]-2}[Code]),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Code"}, #"Table (2)", {"Code"}, "Table (2)", JoinKind.LeftOuter),
    MyTable = Table.ExpandTableColumn(#"Merged Queries", "Table (2)", {"Seq"}, {"Seq"}),
    #"Added Custom1" = Table.AddColumn(MyTable, "Number", each if [Code] = MyTable{0}[Code] 
    then 1 
    else 
        if [Code] <> [PreviousCode] and [RT] <= 20
            then 
                [Seq] -1
            else 
                [Seq])
in
    #"Added Custom1"

Table (2)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjQ3MbcwUtJRMgNiQzCO1UESNgQRRljELYCEMaa4kQmQMMEUNzYAEqYIcTMzUwOQkLEZ1G4jVHETkCZziHgsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Code = _t, RT = _t, Index = _t, #"Desired Result" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", Int64.Type}, {"RT", Int64.Type}, {"Index", Int64.Type}, {"Desired Result", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "PreviousCode", each if [Index] = 1 then [Code] else  #"Changed Type"{[Index]-2}[Code]),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Code"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
    #"Added Index" = Table.AddIndexColumn(#"Removed Duplicates", "Index", 1, 1, Int64.Type),
    #"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"Index", "Seq"}})
in
    #"Renamed Columns"

Screenshot solution

[JW_van_Holst]

I think I misunderstand your problem. As I read it, it is a simple added column with:

Table.AddColumn(#"Changed Type", "Custom", each if [Running Total] >= 20 then 2 else 1)

[Jimmy801]

Hello @rhys1987 

I'm not sure if I got your request right. Add a new column that shows 1 and when running total is >=20 then 2?

If it's like that add a new column with this formula

if[Running Total]>=20 then 2 else 1

Here the complete example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTJTitWBsAyNEEwLONPIBM40NgAzjUBMMzjTBKgtFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Code = _t, #"Running Total" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", Int64.Type}, {"Running Total", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if[Running Total]>=20 then 2 else 1)
in
    #"Added Custom"

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy