Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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!
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"
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.