Custom column for counting processes with different IDs.

[BlueSub]

Hi,

I'm new to Power Query with M codes.

I'm trying to finde out how often an ID in my table reaches status 40 to 60 or higher. If a status 40 doesn't continuously reach status 60, the count isn't looped and only starts again with a new status 40 and counting starting new. 

See the screenshot for better understanding. 

Does anyone have an idea what this might look like in Power Query?

Many thanks in advance

best regards
BlueSub

[v-karpurapud]

Hi @BlueSub 

Could you please confirm if your query have been resolved the solution provided by @MarkLaf & @MarkLaf ? If they have, kindly mark the helpful response and accept it as the solution. This will assist other community members in resolving similar issues more efficiently.

Thank you

[jennratten]

Hello @BlueSub - Thanks for posting in the Fabric Community.  Below is how you can achieve the result, with the assumption that the count is calculated as the number of times the ID, on a given date, is greater than or equal to 40 and less than or equal to 60.  Please let me know if you have any questions. 

let
    // Sample data table with ID, Date, and Value columns
    Source = Table.FromRecords({
        [ID = 1, Date = #date(2025, 5, 1), Value = 40],
        [ID = 1, Date = #date(2025, 5, 1), Value = 50],
        [ID = 1, Date = #date(2025, 5, 2), Value = 60],
        [ID = 2, Date = #date(2025, 5, 1), Value = 20],
        [ID = 2, Date = #date(2025, 5, 1), Value = 30],
        [ID = 2, Date = #date(2025, 5, 2), Value = 40],
        [ID = 3, Date = #date(2025, 5, 1), Value = 10],
        [ID = 3, Date = #date(2025, 5, 1), Value = 20],
        [ID = 3, Date = #date(2025, 5, 2), Value = 30]
    }),
    
    // Add a custom column that returns the value from one row below
    AddNextRowValue = Table.AddColumn(Source, "NextRowValue", each try Source[Value]{[ID]-1} otherwise null),
    
    // Group by ID and Date, and add a custom column that counts the number of times the ID has a value greater than 20 for a given date
    GroupedTable = Table.Group(Source, {"ID", "Date"}, {
        {"AllData", each _, type table [ID=Int64.Type, Date=Date.Type, Value=Int64.Type]},
        {"CountGreaterThan20", each List.Count(List.Select([Value], each _ >= 40 and _ <= 60)), Int64.Type}
    }),
    
    // Keep only the aggregated column and the result.
    #"Removed Other Columns" = Table.SelectColumns(GroupedTable,{"AllData", "CountGreaterThan20"}),
    
    // Expand the grouped table to include the original columns and the new custom column
    ExpandedTable = Table.ExpandTableColumn(#"Removed Other Columns", "AllData", {"ID", "Date", "Value"}),

    // Sort the results
    #"Sorted Rows" = Table.Sort(ExpandedTable,{{"ID", Order.Ascending}, {"Date", Order.Ascending}, {"Value", Order.Ascending}})
in
    #"Sorted Rows"

[BlueSub]

Hello @jennratten

Thanks for Your help and Your reply.

Perhaps I have not expressed myself clearly.
The result should then look as simulated in the “desired result” column. I have attached a screenshot with the result from your code and my desired result. Each new loop (status 40 to status 60) with the same ID should be increased by one count in my "Count" column.

[AlienSx · Accepted solution]

let
    fx_seq = (tbl) => 
        [
            val = List.Buffer(tbl[Value]),
            seq_list = List.Zip({val, List.Skip(val, 1), List.Skip(val, 2)}), 
            positions = List.Buffer(List.PositionOf(seq_list, {40, 50, 60}, Occurrence.All)), 
            tbl_to_join = #table(
                {"desired result", "i"},
                List.TransformMany(
                    List.Positions(positions),
                    (x) => List.Numbers(positions{x}, 3),
                    (x, y) => {x + 1, y}
                )
            ), 
            result = Table.Join(Table.AddIndexColumn(tbl, "idx"), "idx", tbl_to_join, "i", JoinKind.LeftOuter), 
            sort = Table.RemoveColumns(Table.Sort(result, "idx"), {"idx", "i"})
        ][sort],
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], 
    group = Table.Group(Source, "ID", {"x", fx_seq}), 
    z = Table.Combine(group[x])
in
    z

[BlueSub]

Hi, sorry for my late response. The code fits perfectly and calculates the loops amazingly fast. I am thrilled! Many thanks for the support.

[BlueSub]

Hi, sorry for my late response. The code fits perfectly and calculates the loops amazingly fast. I am thrilled! Many thanks for the support.

[MarkLaf]

Using the below data (next time, please paste your data in as a table that we can easily copy/paste into Excel or with 'Enter Data'):

Table

ID	Date	Value
1	1	30
1	2	40
1	3	50
1	4	60
1	5	65
1	6	40
1	7	50
1	8	60
1	9	65
1	10	40
1	11	50
1	12	30
1	13	40
1	14	50
1	15	60
1	16	30
1	17	40
1	18	50
1	19	60
1	20	30
2	1	20
2	2	30
2	3	40
2	4	50
3	1	40
3	2	50
3	3	60
4	1	20
4	2	30
4	3	40
4	4	50
4	5	60
4	6	100
4	7	110
4	8	40
4	9	55
4	10	60

Note: Just entered [Date] as integer instead of writing out a bunch of datetimes. Below should work regardless.

The following M produces the desired column, I think.

let
    Source = Table,
    Sort = Table.Sort(Source,{{"ID", Order.Ascending}, {"Date", Order.Ascending}}),

    AddSplits = Table.AddColumn(
        Sort, "40-50-60 Splits", 
        each List.Contains( {40,50,60}, [Value] ),
        type logical 
    ),

    GroupSplits = Table.Group(
        AddSplits, {"ID", "40-50-60 Splits"}, 
        {{"rows", each _, Value.Type(AddSplits) }}, 
        GroupKind.Local
    ),

    AddCheck = Table.AddColumn(
        GroupSplits, "40-50-60 Is Complete", 
        each [rows][Value] = { 40, 50, 60 }, type logical 
    ),

    AddCheckTypeWithCountInt = type table Type.ForRecord( 
        Record.AddField( 
            Type.RecordFields( Type.TableRow( Value.Type( AddCheck ) ) ), 
            "Count", [Type=Int64.Type,Optional=false] 
        ), 
        false 
    ),

    NoCountRows = Table.SelectRows( AddCheck, each not [#"40-50-60 Is Complete"] ),

    CountRows = Table.SelectRows( AddCheck, each [#"40-50-60 Is Complete"] ),

    GroupAndCount = Table.Group(
        CountRows, {"ID"}, 
        {{"count_rows", each Table.AddIndexColumn( _, "Count", 1 ), AddCheckTypeWithCountInt }}, 
        GroupKind.Local 
    ),

    ExpandCounts = Table.ExpandTableColumn(
        GroupAndCount, "count_rows", 
        {"40-50-60 Splits", "rows", "40-50-60 Is Complete", "Count"}, 
        {"40-50-60 Splits", "rows", "40-50-60 Is Complete", "Count"}
    ),

    Recombine = Table.Combine( { ExpandCounts, NoCountRows } ),

    RemoveTempCols = Table.RemoveColumns(Recombine,{"40-50-60 Splits", "40-50-60 Is Complete"}),

    ExpandRows = Table.ExpandTableColumn(RemoveTempCols, "rows", {"Date", "Value"}, {"Date", "Value"}),
    
    // For some reason, {{"ID", Order.Ascending}, {"Date", Order.Ascending}} 
    // is not working as expected (GroupKind.Local shenanigans?), so using this custom sort funciton
    Resort = let maxStepSize = Int64.From( List.Max( ExpandRows[Date] ) ) + 1 in 
        Table.Sort(ExpandRows, each [ID] + Int64.From( [Date] ) / maxStepSize )
in
    Resort

Output:

[MarkLaf]

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:

ID	Date	Value
5	1	40
5	2	50
5	3	40
5	4	50
5	5	60

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:

[BlueSub]

Hi, sorry for my late response. My table contains 800 000 lines. I have tested the code, but it takes an extremely long time to process, but thanks for the support.