Forum Discussion

Borghi's avatar
Borghi
Advocate I
8 years ago
Solved

Converting Text into Date

Hi folks,   The dataset I am currently work has a text field called [Last Executed On]. It contains "0" or a date in the following format: yyyyMMddhhmmss.   How can I convert it to a Date (or Da...
Need Help
Tips and Tricks
  • MarcelBeug's avatar
    MarcelBeug
    8 years ago

    Yes, the first double quotes of the table name must be preceded by a hash: #

     

    #"Inserted T" = Table.TransformColumns(#"_RSALL_FULL_PERNR-USERS",{{"Last Executed On", each if _ = "0" then null else Text.Insert(_,8,"T"), type text}}),
MarcelBeug's avatar
MarcelBeug
Community Champion
8 years ago

Insert a "T" between the date and the time (and replace "0" by null). Then you can convert to date/time:

 

let
    Source = #table(type table[datetime = text],{{"20171031154130"},{"0"}}),
    #"Inserted T" = Table.TransformColumns(Source,{{"datetime", each if _ = "0" then null else Text.Insert(_,8,"T"), type text}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Inserted T",{{"datetime", type datetime}})
in
    #"Changed Type"