Forum Discussion
AdministratorIssues with Date data type
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.
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!
2 Replies
- Vijay_A_Verma
Most Valuable Professional
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_AdminYes it works, and it's a bonus to know it's a PQ issue and not something I was doing wrong 😉
Thank you Vijay!
