PQ - Restart numbering with table

[sandy666]

SiteDate of initiationRecord numberSiteDate of initiationRecord numbers

1A2/4/2019AL-4PC-2019-00011A
04/02/2019AL-4PC-2019-0001

1A3/23/20191A
23/03/2019AL-4PC-2019-0002

1A9/18/20201A
18/09/2020AL-4PC-2020-0001

1A7/12/20211A
12/07/2021AL-4PC-2021-0001

1A8/23/20211A
23/08/2021AL-4PC-2021-0002

1A9/3/20211A
03/09/2021AL-4PC-2021-0003

1A5/19/20221A
19/05/2022AL-4PC-2022-0001



// 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(Pr efix, {{"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