Site Date of initiation Record number Site Date of initiation Record numbers
1A 2/4/2019 AL-4PC-2019-0001 1A
04/02/2019
 AL-4PC-2019-0001
1A 3/23/2019 1A
23/03/2019
 AL-4PC-2019-0002
1A 9/18/2020 1A
18/09/2020
 AL-4PC-2020-0001
1A 7/12/2021 1A
12/07/2021
 AL-4PC-2021-0001
1A 8/23/2021 1A
23/08/2021
 AL-4PC-2021-0002
1A 9/3/2021 1A
03/09/2021
 AL-4PC-2021-0003
1A 5/19/2022 1A
19/05/2022
 AL-4PC-2022-0001


Code:
// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Locale = Table.TransformColumnTypes(Source, {{"Date of initiation", type date}}, "en-US"),
    Year = Table.AddColumn(Locale, "Year", each Date.Year([Date of initiation]), Int64.Type),
    Index = Table.AddIndexColumn(Year, "Index", 1, 1),
    Grp = Table.Group(Index, {"Year"}, {{"Count", each _, type table}}),
    Sort = Table.TransformColumns(Grp,{{"Count", each Table.AddIndexColumn(Table.Sort(_, "Index"),"GroupIndex")}}),
    Exp = Table.ExpandTableColumn(Sort, "Count", {"Site", "Date of initiation", "Record number", "Index", "GroupIndex"}, {"Site", "Date of initiation", "Record number", "Index", "GroupIndex"}),
    PlusOne = Table.TransformColumns(Exp, {{"GroupIndex", each _ + 1, type number}}),
    FD = Table.FillDown(PlusOne,{"Record number"}),
    EFC = Table.TransformColumns(FD, {{"Record number", each Text.Start(_, 7), type text}}),
    Prefix = Table.TransformColumns(EFC, {{"GroupIndex", each "-000" & Text.From(_, "en-GB"), type text}}),
    TCC = Table.CombineColumns(Table.TransformColumnTypes(Prefix, {{"Year", type text}, {"GroupIndex", type text}}, "en-GB"),{"Record number", "Year", "GroupIndex"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Record numbers"),
    Date = Table.TransformColumnTypes(TCC,{{"Date of initiation", type date}}),
    Rem = Table.RemoveColumns(Date,{"Index"})
in
    Rem