Forum Discussion

loic_bouscaud's avatar
loic_bouscaud
Regular Visitor
8 years ago
Solved

[M language] Transform column type to datetime with 2 string formats?

Hi all,   I have a column timestamp as string. In this column we can find two format of datetime dd/mm/yyyy hh:mm:ss (European datetime) and mm/dd/yyyy hh:mm:ss AM/PM (USA datetime). I want to con...
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    8 years ago

    You shouldn't replace the "_" at all.

     

    My intenton was to have you just follow the indicated steps and only adjust the generated code by adding the if .. then .. else part,

     

    But if you want to copy and adjust the code, then change the previous step name and the column reference.

    According to your example code, this would be:

     

    #"Convert Date" = Table.TransformColumns(#"Expanded Document",{{"Document.timestamp", each Date.From(DateTimeZone.From(_, if Text.Contains(_,"M") then "en-US" else "nl-NL")), type date}})
MarcelBeug's avatar
MarcelBeug
Icon for Community Champion rankCommunity Champion
8 years ago

Select the column, choose transform tab - Date - Parse and adjust the generated code to determine the culture (the if .. then ..else in the code below):

 

= Table.TransformColumns(Source,{{"Document.Timestamp", each Date.From(DateTimeZone.From(_, if Text.Contains(_,"M") then "en-US" else "nl-NL")), type date}})

 

  • loic_bouscaud's avatar
    loic_bouscaud
    Regular Visitor
    8 years ago

    Thanks MarcelBeug for your answer.

    I tried your to use your code and replaced the "_" by my timestamp column but I still got the same error.

    What should I use to replace the "_"?

    • MarcelBeug's avatar
      MarcelBeug
      Icon for Community Champion rankCommunity Champion
      8 years ago

      You shouldn't replace the "_" at all.

       

      My intenton was to have you just follow the indicated steps and only adjust the generated code by adding the if .. then .. else part,

       

      But if you want to copy and adjust the code, then change the previous step name and the column reference.

      According to your example code, this would be:

       

      #"Convert Date" = Table.TransformColumns(#"Expanded Document",{{"Document.timestamp", each Date.From(DateTimeZone.From(_, if Text.Contains(_,"M") then "en-US" else "nl-NL")), type date}})
      • loic_bouscaud's avatar
        loic_bouscaud
        Regular Visitor
        8 years ago

        Thanks a lot for your help, it's working now.
        But what does the '_' mean in the formula?
        I think it's the solution I was looking for. If I compare the code you send me and the code I made first, there are similar.