This solution adapts the code from here and here, respectively. The only lines i added was the "TrimesToYears" statement, and from #"Changed Type" onwards.
// ...
// To get only the numerals from a string,
// split string into individual characters,
// keep the characters which are numbers,
// combine the list back into one string
#"Duplicate Column" = Table.AddColumn(#"Extracted Text Between Delimiters", "Effective", each Text.Combine(List.RemoveNulls(List.Transform(Text.ToList([Name]), each if Value.Is(Value.FromText(_), type number) then _ else null))), type text),
// ...
// Helper function which turns txt argument into year argument
// txt: original text
// year: 6-digit number, a substring somewhere in txt
GetDate = (txt as text, year as text) =>
let
CountOccurences = Text.PositionOf(txt, year, Occurrence.All),
CheckSequence = List.Transform(CountOccurences, each try Number.From(Text.Range(txt, _, 6)) otherwise null),
SelectSuccess = List.Select(CheckSequence, each _ <> null), // null-checking
Output = List.First(SelectSuccess)
in Output,
// (function) Extracts date based on matching with specific "regex"
// the so-called "regex": must begin with 19,20,...,25 (last two digits of year)
// AND the third character (first digit of month) must be 0 or 1 (i.e. excludes "2022xx")
// i added the TrimesToYears statement
ExtractDate = (txt as text) =>
let
TrimesToYears = List.Select({190..252}, each List.Contains({0, 1}, Number.Mod(_, 10))),
YearsToCheck = List.Transform(TrimesToYears, Text.From),
CheckPosition = List.Transform(YearsToCheck, each GetDate(txt, _)),
OnlySuccessful = List.Select(CheckPosition, each _ > 0),
Result = List.First(OnlySuccessful, null)
in
Result,
#"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns", {{"Effective", type text}}),
// i use ExtractDate function in this line
#"Customized Column" = Table.AddColumn(#"Changed Type", "Customized", each ExtractDate([Effective])),
// Really ugly way to convert 6-digit string to date
#"Created Date" = Table.AddColumn(#"Customized Column" , "Effective Date", each Date.From(#datetime(
Number.FromText(Text.Range(Number.ToText([Customized]),0,2))+2000, // year
Number.FromText(Text.Range(Number.ToText([Customized]),2,2)), // month
Number.FromText(Text.Range(Number.ToText([Customized]),4,2)),0,0,0))), // day, etc.
#"Transformed Type" = Table.TransformColumnTypes(#"Created Date", {{"Effective Date", type date}})
// ...
