cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
HolmesMex
New Member

Issues 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.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

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!

View solution in original post

2 REPLIES 2
Syndicate_Admin
Administrator
Administrator

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!

Helpful resources

Announcements
Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on August 17, 2022 at 8am PDT.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Kudoed Authors
Users online (1,695)