Forum Discussion

rhys1987's avatar
rhys1987
Frequent Visitor
5 years ago

Running total with conditions based on another column

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!

 

 

 

Need Help

5 Replies

  • Jimmy801's avatar
    Jimmy801
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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

     

  • JW_van_Holst's avatar
    JW_van_Holst
    Icon for Resolver IV rankResolver IV
    5 years ago

    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)

  • rhys1987's avatar
    rhys1987
    Frequent Visitor
    5 years ago

    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.

    • JW_van_Holst's avatar
      JW_van_Holst
      Icon for Resolver IV rankResolver IV
      5 years ago

      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

    • Jimmy801's avatar
      Jimmy801
      Icon for Community Champion rankCommunity Champion
      5 years ago

      Hello rhys1987 

       

      sorry I can't follow you. 

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

       

      BR

       

      Jimmy