Count consecutive rows in Power BI that have a value. Get start date and max grade from that group

I have a table with the following data:

I want to do three things:

• First, find consecutive rows where the same record_id has a value in the same AEType columns and count those consecutive rows. A null value in that row would count as a stop.
• Second, I want to find the first visit date those consecutive rows started.
• Finally, I want to combine the issues and their counts into one row.

My expected result is shown here:

I've tried this query, but it's not counting the consecutive values correctly.

Source = table_name,

// Ensure correct types
ChangedTypes = Table.TransformColumnTypes(
    Source,
    {
        {"record_id", type text},
        {"visit_date", type date},
        {"int_date", type date},
        {"sched", type text}
    }
),

// Unpivot AE columns
Unpivoted = Table.Unpivot(
    ChangedTypes,
    {"hb_ae","wbc_ae","anc_ae","plt_ae","ast_ae","alt_ae","arc_hb_ae"},
    "AEType",
    "Grade"
),

// Remove blanks
Filtered = Table.SelectRows(Unpivoted, each [Grade] <> null),

// Sort by record_id, AEType, visit_date
Sorted = Table.Sort(
    Filtered,
    {{"record_id", Order.Ascending},
     {"AEType", Order.Ascending},
     {"visit_date", Order.Ascending}}
),

// Add index per group (record_id + AEType)
WithIndex = Table.Group(
    Sorted,
    {"record_id", "int_date", "sched", "AEType"},
    {
        {"AllRows", each Table.AddIndexColumn(_, "RowInGroup", 1, 1, Int64.Type)}
    }
),
Expanded = Table.ExpandTableColumn(WithIndex, "AllRows",
    {"visit_date","Grade","RowInGroup"}),

// Add DateRank and compute GroupKey = RowInGroup - DateRank
WithKey = Table.Group(
    Expanded,
    {"record_id","int_date","sched","AEType"},
    {
        {"WithKey", each
            let
                t = Table.Sort(_, {{"visit_date", Order.Ascending}}),
                t2 = Table.AddIndexColumn(t, "DateRank", 1, 1, Int64.Type),
                t3 = Table.AddColumn(t2, "GroupKey", each [RowInGroup] - [DateRank])
            in
                t3
        }
    }
 ),
 ExpandedKey = Table.ExpandTableColumn(
    WithKey, "WithKey",
    {"visit_date","Grade","RowInGroup","DateRank","GroupKey"}
 ),

 // Final grouping: continuous blocks
 FinalGrouped = Table.Group(
    ExpandedKey,
    {"record_id","int_date","sched","AEType","GroupKey"},
    {
        {"AEDate", each List.Min([visit_date]), type date},
        {"AEs", each Table.RowCount(_), Int64.Type},
        {"MaxGrade", each List.Max([Grade]), Int64.Type}
    }
 ),

 // Clean up
 Final = Table.SelectColumns(
    FinalGrouped,
    {"record_id","int_date","sched","AEType","AEDate","AEs","MaxGrade"}
 ),

 // Sort final output
 FinalSorted = Table.Sort(Final,
    {{"record_id", Order.Ascending}, {"AEDate", Order.Ascending}})
 in
 FinalSorted

Any help would be appreciated

Try this, given your new data:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"ae Cols" = List.Select(Table.ColumnNames(Source), each Text.EndsWith(_,"_ae")),
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"record_id", Int64.Type}, {"visit_date", type date}, {"int_date", type date}, 
        {"month", Int64.Type}, {"sched", type text}}),
    
//don't need month column for the report    
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"month"}),

//Replace null with <space> so as not to delete null rows when we unpivot
    #"Replace Null with Space" = Table.ReplaceValue(#"Removed Columns",null,"",Replacer.ReplaceValue,#"ae Cols"),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Replace Null with Space", {"record_id", "visit_date", "int_date", "sched"}, "Attribute", "Value"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", Int64.Type}}),

//Need to sort in order to use GroupKind.Local in next step
    #"Sorted Rows" = Table.Sort(#"Changed Type1",{
        {"record_id", Order.Ascending}, {"Attribute", Order.Ascending}, {"int_date", Order.Ascending}, {"visit_date", Order.Ascending}}),

#"Grouped Rows" = Table.Group(#"Sorted Rows", {"record_id", "Attribute","Value"}, {
        {"AEType", each Text.Combine(List.RemoveLastN(Text.Split([Attribute]{0},"_"),1),"_"), type text},

//you do not define your logic for `int_date` although they are all the same in your sample
        {"int_date", each List.Min([int_date]), type date},

        {"AEDate", each List.Min([visit_date]), type date},
        {"AEs", each List.Count(List.RemoveNulls([Value])), Int64.Type},
        {"MaxGrade", each List.Max([Value]), Int64.Type},

//You do not define your logic to determine "Schedule" vs "Not Scheduled" so next line may need to be edited
        {"sched", each List.Last([sched]), type text}

        },GroupKind.Local, (x,y)=>Number.From(
            x[record_id]<>y[record_id]
            or x[Attribute]<>y[Attribute]
            or x[Value]=null or y[Value]=null
            )
            ),
    #"Removed Columns1" = Table.RemoveColumns(#"Grouped Rows",{"Attribute", "Value"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns1", each ([AEs] <> 0))
in
    #"Filtered Rows"

`Results from your revised data: