Error during the transformation from a text time stamp data into a datetime data - possible bug

[Anonymous]

Hi,

I've imported in a query more SQL data having a text time stamp data. I've tried to transform this data into a datetime value: for these brief months,

JAN (GEN in Italian) - january

FEB (FEB in Italian) - february

APR (APR in Italian) - april

MAY (MAG in Italian) - may

JUN (GIU in Italian) - june

JUL (LUG in Italian) - july

AUG (AGO in Italian) - august

SEP (SET in Italian) - september

OCT (OTT in Italian) - october

NOV (NOV in Italian) - november

DEC (DIC in Italian) - december

I've no problems, but for MAR (MAR in Italian) I've a transformation error:

I've solved this issue by replacing the substring "MAR" with "03".

Does this case represent a Power Query bug to solve?

Thanks

[edhans]

FYI @Anonymous - I believe this is now recognized as a confirmed bug and a fix will be forthcoming. Cannot speak to the timeline, but I suspect Power BI's fix will come first and Excel's later. Both use the same Power Query engine but Power BI has a faster release cadence as a rule.

Until then, use the Replace code I have suggested. If you could marke one or more of these answers as the solution so others will recognize the answer it would be appreciated.

[edhans]

I am trying to get an answer to this. It seems to me it should, but there could be an issue here you and I are not seeing. That said, if you add a new column and use this formula it will work:

try DateTime.FromText([Column1], "it-IT") otherwise DateTime.FromText([Column1], "en-US")

Then you can delete the orginal column.

I will follow up if I get a better answer here @Anonymous. I did try the 4 culture codes Power Query understands for Italian, and none worked with MAR, but MAR also works for the US. But hopefully this will get you on your way with the project until a better answer surfaces.

Full code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDU9XUM0jUyUDAwsDI1szI0UIrVAQu7e4aiCscCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Date", each try DateTime.FromText([Column1], "it-IT") otherwise DateTime.FromText([Column1], "en-US"), DateTime.Type)
in
    #"Added Custom"

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[edhans]

@Anonymous - there could be a conflict here. My understanding is Mar is March/Marzo, or Tuesday/Martedi, and perhaps PQ is guessing which it is incorrectly. I tried setting it to lowercase, but 01-mar-20 also fails.

For now, you should replace MAR with MARZO per the below code then use the it-IT culture code and then it works. Still investigating though as it is counter intuitive that it should use the weekday first as the conversion before the month, especially when it is properly detecting the first digits as the days and the last digits as the year.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDU9XUM0jUyUDAwsDI1szI0UIrVAQu7e4ZiE/Z1dAcJGxpZGRtYmZgqxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"MAR","MARZO",Replacer.ReplaceText,{"Column1"}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Replaced Value", {{"Column1", type datetime}}, "it-IT")
in
    #"Changed Type with Locale"

[Anonymous]

Hi all, many thanks for your replies 😊

As I said, I've solved the issue replacing "MAR" with "03", but also replacing with "MARZO" is a good idea.

It seems that for PQ "MAR" is an ambiguous brief term and so PQ isn't able to distinguish between MARZO in Italian (March in English) and MARTEDI' in Italian (Tuesday in English).

I think that a such case is a PQ bug not a development mistake, that it could alway occur if this bug shouldn't be solved.

[edhans]

Agreed. I think for now this is solved - can you mark one or more answers as the solution.

I am making inquiries though to MS about this because you and I are on the same pages @Anonymous - This seems to be a bug or poor design decision on how MAR is handled. But, even if it is determined to be a bug a fix wouldn't be immediate. I think the Replace Values and then the transformation are your best bet for the immediate future.

I have bookmarked this thread so next week or in 3-6 months, whenever a better answer surfaces, I will respond.

[Anonymous]

if you use the complete word for the 3.th month which is marzo it seems working:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDU9U0sqsrXNTJQitUB8909Q8G8WAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Modificato tipo" = Table.TransformColumnTypes(Source,{{"Column1", type date}})
in
    #"Modificato tipo"

the problem may have to do with the fact that "mar" are ALSO the initial letters of "martedì"  which is the second day of the week.

PS

Les amis français semblent avoir le même problème lorsqu'ils utilisent "jui" qui est ambigu pour juin et juillet