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

convertTo.pq

let
    /* Function to convert an input value like a date, datetime, datetimezone, or text to a target type */
    convertTo = let
        fxConvertTo = (Value as any, ReturnType as text, optional Culture as text) as any =>
            let
                TypeSelector = List.PositionOf(AvailableTypes, ReturnType, 0, Comparer.OrdinalIgnoreCase),
                BaseDate = DateTimeZone.From(Value, (Culture ?? Culture.Current)),
                TypeValue = {Date.From(BaseDate), DateTime.From(BaseDate), BaseDate}{TypeSelector}
            in
                TypeValue,
        AvailableTypes = {"Date", "DateTime", "DateTimeZone"},
        fxWithDocumentation = 
        let
            ValueParameter = type any meta [
                Documentation.FieldCaption = "Value",
                Documentation.FieldDescription = "A value to be converted. This value can 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.",
                Documentation.AllowedValues = AvailableTypes
            ],
            CultureParameter = type text meta [
                Documentation.FieldCaption = "optional Culture",
                Documentation.FieldDescription = "Specify a Culture to interpret the input value, for example: ""en-US"" or ""en-GB"". Defaults to the current culture."
            ], 
            addParameterTypes = type function (
                Date as ValueParameter, 
                ReturnType as ReturnTypeParameter,
                optional Culture as CultureParameter
            ) as any, 
            addFunctionType = Value.ReplaceType(fxConvertTo, addParameterTypes)
        in
            addFunctionType
    in
        fxWithDocumentation,
    
    /* A sample for illustration to invoke the function on */
    /* Depending on your Culture an error will appear in one of the bottom two rows */
    Source = Table.FromColumns(
        {{#date(2025, 6, 11), #datetime(2025, 6, 11, 0, 0, 0), #datetimezone(2025, 6, 11, 0, 0, 0, 0, 0), 
            "2025-06-11 09:10", "2025.6.11 17:31", "2025/6/11 9:10 pm", null, "25-06-2025", "6-25-2025"}},
        {"Value"}
    ),
    
    /* Return a Date type value */
    InvokeFunction1 = Table.AddColumn(Source, "Convert to Date", each convertTo([Value], "Date")),
    
        
    /* Return a DateTime type value */
    InvokeFunction2 = Table.AddColumn(Source, "Convert to Date", each convertTo([Value], "Datetime")),
    
        
    /* Return a DateTimeZone type value */
    InvokeFunction3 = Table.AddColumn(Source, "Convert to Date", each convertTo([Value], "Datetimezone"))
in
    InvokeFunction3