Forum Discussion
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 Datetime) format?
Thank you in advance!
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}}),
8 Replies
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"
MarcelBeug wrote: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"Hi Marcel,
Thank you for the info.
Where should I type this code?
In the Query Editor.
You must have a query for your table. Select that query, go into the Advanced Editor, and replace the last 2 lines ("in" followed by the last step name), by the last 4 lines of the query above (starting with step #"Inserted T") and replace "Source" with the name of the preceding step in your query).
#"Inserted T" = Table.TransformColumns(<NameOfPrecedingStep>,{{"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"
