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}}),
Borghi's avatar
Borghi
Advocate I
8 years ago

Hi Marcel,

 

Thanks again!

 

I replaced the code, and now it looks like this:

let
    Source = Access.Database(File.Contents("Z:\Corporate\GRC\FullRiskAnalysis.accdb"), [CreateNavigationProperties=true]),
    #"_RSALL_FULL_PERNR-USERS" = Source{[Schema="",Item="RSALL_FULL_PERNR-USERS"]}[Data],
    #"Inserted T" = Table.TransformColumns("_RSALL_FULL_PERNR-USERS",{{"Last Executed On", each if _ = "0" then null else Text.Insert(_,8,"T"), type text}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Inserted T",{{"datetime", type datetime}})
in
    #"Changed Type"

 

However, it is showing the following error message:

"Expression.Error: We cannot convert the value "_RSALL_FULL_PERNR-US..." to type Table.
Details:
    Value=_RSALL_FULL_PERNR-USERS
    Type=Type"

 

Any thoughts?

Thanks in advance

MarcelBeug's avatar
MarcelBeug
Community Champion
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}}),
  • Borghi's avatar
    Borghi
    Advocate I
    8 years ago

    It worked!

     

    Thank you so much!