Creating a new column to count consecutive instances of another column

Hello

this could come from a different sort order after expanding.

here now a solution that should definitily work

let
	Source = #table
	(
		{"Primary Key","Attendance Code","Attendance Date"},
		{
			{"1","P","40544"},	{"1","P","40575"},	{"1","A","40603"},	{"1","P","40634"},	{"1","A","40664"},	{"1","A","40695"}
		}
	),
    ToDate = Table.TransformColumns
    (
        Source,
        {
            {
                "Attendance Date",
                each Date.From(Number.From(_)),
                type date
            }
        }
    ),
    CreateIndex = Table.Buffer(Table.AddIndexColumn
    (
        ToDate,
        "FirstIndex",
        1,
        1
    )),
    Group = Table.Group
    (
        CreateIndex, 
        {"Attendance Code"}, 
        {{"AllRows", (tableint) => let 
            AddIndex = Table.AddIndexColumn(tableint,"Index",1),
            AddColumn = Table.AddColumn(AddIndex,"New Column", each if _[Index]=Table.RowCount(AddIndex) then Table.RowCount(AddIndex)  else null),
            DeleteIndex = Table.RemoveColumns(AddColumn,"Index")
        in 
            DeleteIndex}},
        GroupKind.Local,
        (group,current)=> if group[Attendance Code]=current[Attendance Code] then 0 else 1),
    DeleteOther = Table.SelectColumns(Group,{"AllRows"}),
    ExpandTable = Table.ExpandTableColumn(DeleteOther, "AllRows", {"Primary Key", "Attendance Code", "Attendance Date","New Column","FirstIndex"}, {"Primary Key", "Attendance Code", "Attendance Date", "New Column","FirstIndex"}),
    ChangeType = Table.TransformColumnTypes(ExpandTable,{{"New Column", Int64.Type}, {"Attendance Date", type date}, {"Attendance Code", type text}, {"Primary Key", type text}, {"FirstIndex", Int64.Type}}),
    Sort = Table.Sort(ChangeType,{{"FirstIndex", Order.Ascending}}),
    DeleteFirstIndex = Table.RemoveColumns(Sort,{"FirstIndex"})
in
	DeleteFirstIndex

Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy