extractEightDigitSequence M function | Extract only 8 digit sequences such as a date tag from a string.

extractEightDigitSequence.pq

let
    fxExtractEightDigitSequence = let 
        extractEightDigitSequence = (txt as nullable text, optional Occurrence as number) =>
        let
            input = txt ?? "",
            parts = Text.SplitAny(input, Text.Remove(input, {"0" .. "9"})),
            digits = List.Select(parts, each Text.Length(_) = 8)
        in
            {List.First(digits), List.Last(digits), digits}{(Occurrence ?? 0)},
        extractEightDigitSequenceType = type function (
            txt as (type nullable text meta [
                Documentation.FieldCaption = "txt",
                Documentation.FieldDescription = 
"A text string to scan for 8-digit sequences (e.g. ""Sales20250109T12_45.csv""). "
            ]),
            optional Occurrence as (type number meta [
                Documentation.FieldCaption = "Occurrence",
                Documentation.FieldDescription =
"Controls which value to return: 
0 – Occurrence.First, the first 8-digit match (default)
1 – Occurrence.Last, the last 8-digit match
2 – Occurrence.All, all 8-digit matches as a list",
             Documentation.AllowedValues = { 0, 1, 2 }
            ])
        ) as any meta [
            Documentation.Name            = "extractEightDigitSequence",
            Documentation.LongDescription = "Returns only 8 digit sequences from the input string. Defaults to occurrence first.",
            Documentation.Category        = "Text",
            Documentation.Version         = "1.00: Initial Version",
            Documentation.Author          = " Melissa de Korte",
            Documentation.Examples        = {
                [
                    Description = "Default (occurrence first)",
                    Code        = "extractEightDigitSequence(""Sales20250109T12_45.csv"")",
                    Result      = "20250109"
                ],
                [
                    Description = "Occurance all",
                    Code        = "extractEightDigitSequence(""US 01032025 GB 03012025 EU 05012025.csv"", 2)",
                    Result      =  "{ ""01032025"", ""03012025"", ""05012025"" }"
                ]
            }
        ]
    in Value.ReplaceType(extractEightDigitSequence, extractEightDigitSequenceType),
    /* A sample for illustration to invoke the function on */
    Source = Table.FromRows(
        {
            {"35500 Sales_20241203T12_45.csv"},
            {"US 01032025 EU 03012025.csv"},
            {"NA03012025sales.csv"},
            {"US 01032025 GB 03012025 EU 05012025.csv"}
        },
        type table[Filename=text]
    ),
    /* Create a Field for each occurrence option */
    AddFields = Table.AddColumn(Source, "n", each [
            First = fxExtractEightDigitSequence([Filename]), 
            Last = fxExtractEightDigitSequence([Filename], 1), 
            All = fxExtractEightDigitSequence([Filename], 2)
        ], 
        type [First=text, Last=text, All={text}]
    ),
    ExpandFields = Table.ExpandRecordColumn(AddFields, "n", {"First", "Last", "All"})
in
    ExpandFields