Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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

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

Need Help

6 Replies

  • edhans's avatar
    edhans
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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.

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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

    • edhans's avatar
      edhans
      Icon for Community Champion rankCommunity Champion
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago

        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's avatar
    edhans
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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.