Count consecutive values with filter

[chriser]

Hi, 

I would like to count consecutive values with a filter as per below example.

• OrderNumber is unique
• For not registered users, return 0

CustomerID     OrderNumber     Registered                    Result

1                      A                          Yes                               1

2                      B                          No                                0

3                      C                          Yes                               1

1                      D                          Yes                               2

2                      E                           No                               0

1                      F                           Yes                               3

3                      G                          Yes                               2

Thanks in advance

[ChrisMendoza]

@chriser -

Power Query as a possible solution:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIE4sjUYqVYnWglIyDbCYj98sFcYyDTGUkapNwFTbkrQjlI2g1JGqTdHcaPBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [CustomerID = _t, OrderNumber = _t, Registered = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CustomerID", Int64.Type}, {"OrderNumber", type text}, {"Registered", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"CustomerID"}, {{"Count", each _, type table [CustomerID=number, OrderNumber=text, Registered=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Trans", 1,1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"OrderNumber", "Registered", "Trans"}, {"OrderNumber", "Registered", "Trans"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Result", each if [Registered] = "Yes" then [Trans] else 0),
    #"Sorted Rows" = Table.Sort(#"Added Custom1",{{"OrderNumber", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Count", "Trans"})
in
    #"Removed Columns"

[AlB · Accepted solution]

Hi @chriser 

Try this for a calculated column:

Result =
IF (
    Table1[Registered] = "Yes",
    CALCULATE (
        COUNT ( Table1[CustomerID] ),
        ALLEXCEPT ( Table1, Table1[CustomerID] ),
        Table1[OrderNumber] <= EARLIER ( Table1[OrderNumber] )
    ),
    0
)

[chriser]

Thanks @AlB! Works really well, although this approach is memory hungry. My poor Surface failed to deliver results on over 1m rows, but after filtering table to 8k rows it worked well. Thanks a lot!