Issues with Date data type

[Syndicate_Admin]

I have a CSV source with dates in Spanish like this: "1-ENE-22" or "16-DIC-21".  When changing data type to date it shows error.  Tried "Using Locale" and selecting Spanish, still got error.

In excel, using the "value" formula results in the date number, so it should work in power query.

One thing I noticed is that Excel language is Spanish, but PQ ribbon is in English.  In query options I have Spanish.

[Vijay_A_Verma]

This is a known issue with 3 letters month names for international dates for few cultures. While 3 letters month names work well with English language, it breaks for few including Spanish. Hence, these 3 letters of month names need to be converted to full month names, then your conversion will work fine.

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjbUdfF01jUyVIrViVYyNNJ19XPVNTIC89AIVEl8PCNDFJ4Jbp6xIW4e0MzUvNSifLDjYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FECHA = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Date.FromText(try Text.ReplaceRange([FECHA],3,3,{"ENERO", "FEBRERO", "MARZO", "ABRIL", "MAYO", "JUNIO", "JULIO", "AGOSTO", "SEPTIEMBRE", "OCTUBRE", "NOVIEMBRE", "DICIEMBRE"}{List.PositionOf({"ENE", "FEB", "MAR", "ABR", "MAY", "JUN", "JUL", "AGO", "SEP", "OCT", "NOV", "DIC"},Text.Range([FECHA],3,3))}) otherwise "1-ENERO-1900",[Culture="es-ES"]), type date)
in
    #"Added Custom"

[Syndicate_Admin · Accepted solution]

Yes it works, and it's a bonus to know it's a PQ issue and not something I was doing wrong 😉

Thank you Vijay!