Custom column for counting processes with different IDs.

I realized the above does not work with a sequence like, { 40, 50, 40, 50, 60 } in value. E.g. if I add this to my original test data:

Does not work as all rows go into the group and then the check of {40,50,40,50,60} = {40,50,60} fails the test and doesn't count. Within each group, we could iterate through all subsets with size equal to the target sequence (e.g., check {40,50,40}, {50,40,50}, {40,50,60}), but that was starting to feel too cumbersome.

Here is an alternative approach that works with the above:

let
    // Set Sequence to count. Order matters.
    TargetSequence = {40, 50, 60},

    // Calculate count of sequence.
    SequenceCount = List.Count(TargetSequence),

    // Source, ensure it is properly sorted for comparison.
    Source = Table,
    Sort = Table.Sort(Source, {{"ID", Order.Ascending}, {"Date", Order.Ascending}}),

    // List out all possible ID slices where slice size = sequence count.
    IdSlices =
        let
            IDs = List.Buffer(Sort[ID])
        in
            List.Zip(
                List.Generate(
                    () => 0, each _ < SequenceCount, each _ + 1, each
                        List.RemoveFirstN(IDs, _) & List.Repeat({null}, _)
                )
            ),

    // List out all possible Value slices where slice size = sequence count.
    ValueSlices =
        let
            vals = List.Buffer(Sort[Value])
        in
            List.Zip(
                List.Generate(
                    () => 0,
                    each _ < SequenceCount,
                    each _ + 1,
                    each List.RemoveFirstN(vals, _) & List.Repeat({null}, _)
                )
            ),
    
    // For each row of source table, flag the ranges that are
    // in same ID and match the target sequence.
    IdsAndGoodRanges = List.Generate(
        // For each row of source table...
        () => 0, each _ < Table.RowCount(Source), each _ + 1,
        each
            // With ID slice and value slice of current row
            let curIds = IdSlices{_}, curVals = ValueSlices{_} in
            // If all IDs in Id slice are the same
            // and the value slices match the target sequence
            if List.Count(List.Distinct(curIds)) = 1 
                and curVals = TargetSequence
            // Then return the ID and positions of values matching the target sequence
            then {
                List.First(curIds), 
                {_ + 1.._ + SequenceCount}
            }
            // Else return null
            else null
    ),

    // Create the table of IDs and positions of values that matched target sequence.
    ToTable = Table.FromRows(
        List.RemoveNulls(IdsAndGoodRanges), 
        type table [Id = Int64.Type, Good Range = {Int64.Type}]
    ),

    // Group by ID and add an index to the position ranges of matched values.
    // This provides the count of matched values within each ID.
    GroupIdsAndCountGoodRanges = Table.Group(
        ToTable,
        {"Id"},
        {
            {
                "rows",
                each Table.AddIndexColumn(Table.RemoveColumns(_, {"Id"}), "Count", 1),
                type table [Good Range = {Int64.Type}, Count = Int64.Type]
            }
        }
    ),

    // Expand the postion ranges and counts of matched values.
    ExpandCountedRows = Table.ExpandTableColumn(
        Table.RemoveColumns(GroupIdsAndCountGoodRanges, {"Id"}),
        "rows",
        {"Good Range", "Count"},
        {"Good Range", "Count"}
    ),

    // Expand the positions to their own rows and set the expanded column as key.
    // This improves performance of the join.
    ExpandRangesWithCounts = Table.AddKey(
        Table.ExpandListColumn(ExpandCountedRows, "Good Range"), {"Good Range"}, true
    ),

    // Reference the original table and add an index to it.
    // Again, this improves performance of the join.
    OrigWithIndex = Table.AddKey(Table.AddIndexColumn(Sort, "Index", 1), {"Index"}, true),

    // Join positions of matched values and their counts to the original table.
    JoinCounts = Table.NestedJoin(
        OrigWithIndex, "Index", 
        ExpandRangesWithCounts, "Good Range", 
        "JoinedCounts", JoinKind.LeftOuter
    ),

    // Expand the counts of matched values.
    ExpandCounts = Table.ExpandTableColumn(JoinCounts, "JoinedCounts", {"Count"}, {"Count"}),

    // Remove the index column as this is no longer needed.
    // Fine to keep if desired, though.
    RemoveSortIndex = Table.RemoveColumns(ExpandCounts, {"Index"})
in
    RemoveSortIndex

Output: