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}})
loic_bouscaud's avatar
loic_bouscaud
Regular Visitor
8 years ago

Thanks for the explanation.

 

Also, I have a problem with the formula you gave me.
When I use it with Date.From(DateTimeZone.From()) there are no problem, but I want to keep the time so I replaced Date.From by DateTime.From.

In the Query Editor I got the result I want but when I apply the changes to Power BI Desktop, an error message appear but there are no details in the generated table errors and I lost all the data in my Desktop model.

Is there a way to keep the datetime or must I create two custom columns and then concatenate them?

MarcelBeug's avatar
MarcelBeug
Community Champion
8 years ago

At the end of my formula, there is type date. Change that to type datetime and you should be fine.