convertDateTo* M function | Convert an input value like a date, datetime, datetimezone or text to a target type.

convertDateTo.pq

let
    /* Function to convert an input value like a date, datetime, datetimezone, or text to a target type */
    convertDateTo = let
        fxConvertDateTo = (Value as any, ReturnType as text, optional Culture as any, optional formatString as text) as any =>
        let
            Separators = Text.Combine(List.Select( 
                Text.ToList(Text.Remove(Text.From(Value) ?? "", {"0" .. "9"})),
                each Text.Lower(_) = Text.Upper(_) )
            ),
            c = Culture ?? Culture.Current,
            getCulture =
                if Value.Is(c, type text) then c
                else let
                    CultureExceptions = if Value.Is(List.First(Culture), type text) then {Culture} else Culture,
                    FirstMatch = List.First(
                        List.Select( CultureExceptions,
                            each List.AnyTrue(
                            List.Transform(Text.ToList(_{0}), (s) => Text.Contains(Separators, s))
                        )
                    ), null ) 
                in if FirstMatch = null then Culture.Current else FirstMatch{1},
            getFormat = Text.Replace(Text.Replace(Text.Replace(formatString, "Y", "y"), "m", "M"), "D", "d"),
            BaseDate = if (Separators = "" and Value.Is(Value, type text)) 
                then try DateTimeZone.From(Date.FromText(Value, [Format=getFormat])) otherwise DateTimeZone.From(Date.FromText(Value))
                else DateTimeZone.From(Value, getCulture),
            TypeSelector = List.PositionOf(AvailableTypes, ReturnType, 0, Comparer.OrdinalIgnoreCase),
            TypeValue = {Date.From(BaseDate), DateTime.From(BaseDate), BaseDate, DateTimeZone.ToText(BaseDate, [Format="yyyy-MM-dd"]), 
                DateTimeZone.ToText(BaseDate, [Format="dd-MM-yyyy"]), DateTimeZone.ToText(BaseDate, [Format="MM-dd-yyyy"]) }{TypeSelector}
        in
            TypeValue,
        AvailableTypes = {"Date", "DateTime", "DateTimeZone", "YMD", "DMY", "MDY"},
        fxWithDocumentation = let
            ValueParameter = type any
                meta [
                Documentation.FieldCaption = "Value",
                Documentation.FieldDescription = "A value to be converted. 
This may be a date, datetime, datetimezone or text (in a format that allows conversion)."
            ],
            ReturnTypeParameter = type text
                meta [
                Documentation.FieldCaption = "ReturnType",
                Documentation.FieldDescription = "Specifies the target output type. 
Options are: Date, DateTime, DateTimeZone, YMD (text), DMY (text), MDY (text).",
                Documentation.AllowedValues = AvailableTypes
            ],
            CultureParameter = type any
                meta [
                Documentation.FieldCaption = "Culture",
                Documentation.FieldDescription = "Optionally specify a Culture to interpret the input value. You may pass:
• a text (e.g. ""en-US""),
• a list with separator(s) and culture as a text (e.g. {""- ."", ""en-GB""} ),
• or a list of lists with separator(s) and culture pairs (e.g. {{""- ."", ""en-GB""}, {""/"", ""en-US""}} ).
If omitted, culture defaults to Culture.Current."
            ],
            FormatStringParameter = type text
                meta [
                Documentation.FieldCaption = "formatString",
                Documentation.FieldDescription = "Optionally specify a formatString to be used when parsing a string without separators. 
Options are: ""ddMMyyyy"" or ""MMddyyyy"" .",
                Documentation.AllowedValues = {"ddMMyyyy", "MMddyyyy"}
            ],
            addParameterTypes = type function (
                Value as ValueParameter,
                ReturnType as ReturnTypeParameter,
                optional Culture as CultureParameter,
                optional formatString as FormatStringParameter
            ) as any,
            addFunctionType = Value.ReplaceType(fxConvertDateTo, addParameterTypes)
        in
            addFunctionType
    in
        fxWithDocumentation,
    
    /* A sample for illustration to invoke the function on */
    Source = Table.FromColumns(
        List.Repeat({{#date(2025, 6, 11), #datetime(2025, 6, 11, 0, 0, 0), #datetimezone(2025, 6, 11, 0, 0, 0, 0, 0), 45819, 45819.575, 
            "20250611", "11062025", "06112025", "2025-06-11 09:10", "2025.6.11 17:31", "2025, 6, 11 9:10 pm", null, "25-06-2025", "6/25/2025",
            "Mar 12 2025", "3 dez 2024", "Dezember 2024"}}, 2),
        {"Value", "Value-Copy"}
    ),
    
    /* Return a Date type value */
    getDate = Table.AddColumn(Source, "Convert to Date", each convertDateTo([Value], "Date")),

    /* Return a DateTime type value */
    getDateTime = Table.AddColumn(Source, "Convert to Date", each convertDateTo([Value], "Datetime")),

    /* Return a DateTimeZone type value */
    getDateTimeZone = Table.AddColumn(Source, "Convert to Date", each convertDateTo([Value], "Datetimezone")),

    /* Return a Text type value in the format DD-MM-YYYY, using culture de-DE*/
    getDate_wCulture1 = Table.AddColumn(Source, "Convert to Date", each convertDateTo([Value], "DMY", "de-DE")),

    /* Return a Text type value in the format MM-DD-YYYY using culture de-DE*/
    getDate_wCulture2 = Table.AddColumn(Source, "Convert to Date", each convertDateTo([Value], "MDY", {"-. ", "de-DE"})),

    /* Return a Text type value in the format YYYY-MM-DD using multiple culture pairs*/
    getDate_wCulture3 = Table.AddColumn(Source, "Convert to Date", each convertDateTo([Value], "YMD", {{"-. ", "de-DE"}, {"/", "en-US"}})),

    /* Return a Text type value in the format DD-MM-YYYY using multiple culture pairs and a formatString to parse strings without separators*/
    getDate_wCulture_formatString = Table.AddColumn(Source, "Convert to Date", each convertDateTo([Value], "DMY", {{"-. ", "de-DE"}, {"/", "en-US"}}, "ddMMyyyy"))
in
    getDate_wCulture_formatString