Row number by group only if condition is true

[Netrelemo]

I am struggling to count how long "the run" is, in each group. 

each group should have a line item every month, but sometimes they don't. 

The challenge is that if a lineitem does not occur in any particular month period, then the "counter" must reset. 

Here's a data sample:

Label	Period	Run
alpha	2023-04-25	1
alpha	2023-05-25	2
alpha	2023-06-25	3
alpha	2023-08-25	1
beta	2023-03-25	1
beta	2023-08-25	1

I can easily use the two index approach to find the run, but I cannot see how to take into account thatthere may be gaps in the group. 

  #"Added Index1" = Table.AddIndexColumn(#"Sorted Rows", "Index1", 0, 1, Int64.Type),
    #"Added Index2" = Table.AddIndexColumn(#"Added Index1", "Index2", 1, 1, Int64.Type),
    #"Merged Self" = Table.NestedJoin(#"Added Index2", {"Index2"}, #"Added Index2", {"Index1"}, "Added Index2", JoinKind.LeftOuter),

Help?

[Netrelemo]

Clever 🙂

Thank you very much. 

[danextian · Accepted solution]

Hi @Netrelemo 

Please try this (beware: the solution is not very straightforward)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSswpyEhU0lEyMjAy1jUw0TUyVYrVQRc2xS5shl3YAiaclFqCEDXGKopdrSVMND0xNxeLEajCJtiFTbELm4OFYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Label = _t, Period = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Label", type text}, {"Period", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Label"}, {{"Grouped", each _, type table [Label=nullable text, Period=nullable date]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Grouped_Index", each Table.AddIndexColumn([Grouped], "Index", 0, 1)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom_Index2", each let 
x = [Grouped_Index],
y = x[Index]
in
Table.AddColumn(x, "Prev", each try x[Period]{[Index]-1} otherwise null)),
    #"Expanded Custom_Index2" = Table.ExpandTableColumn(#"Added Custom1", "Custom_Index2", {"Index", "Period", "Prev"}, {"Index", "Period", "Prev"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom_Index2",{"Grouped", "Grouped_Index"}),
    #"Added Custom2" = Table.AddColumn(#"Removed Columns1", "Diff", each let x = Number.Round(Number.From(([Period]- [Prev])/( 365.25 / 12 )) ,0 ) in if x = null then 1 else x, Int64.Type),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom2",{"Prev"}),
    #"Added to Column" = Table.TransformColumns(#"Removed Columns2", {{"Index", each _ + 1, type number}}),
    #"Added Custom3" = Table.AddColumn(#"Added to Column", "Running Count", each 
let
x = [Label], 
y = Table.SelectRows(#"Added to Column", each [Label] = x) 
in List.Accumulate(List.FirstN(y[Diff],[Index]),  0,
                (state, current) =>
                    if current = 1 then state + 1
                    else 1
            )
        , Int64.Type),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom3",{{"Period", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Index"})
in
    #"Removed Columns"