Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
41 | |
29 | |
23 | |
21 | |
17 |