Forum Discussion
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 ...
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.
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
