Converting Text into Date

[Borghi]

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!

[MarcelBeug]

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"

[Borghi]

---

@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?

[MarcelBeug]

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"

[Borghi]

---

@MarcelBeug wrote:

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"

---

Hi Marcel,

Thanks again for the info.

The original code of my dataset was:

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]
in
    #"_RSALL_FULL_PERNR-USERS"

Then, I changed to this one (column name is [Last Executed On]):

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],{{"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"

PS: I added a "," in the end of second line

But it is showing me an error message:

"Expression.Error: We cannot apply field access to the type Text.
Details:
    Value=_RSALL_FULL_PERNR-USERS
    Key=Last Executed On"

What am I doing wrong?

Thank you in advance

[MarcelBeug]

The first parameter of Table.TransformColumns must be the name of the table (without field reference).

The field is the first element of the part between the double curly brackets.

The corrected code for the #"Insterted T" step:

    #"Inserted T" = Table.TransformColumns("_RSALL_FULL_PERNR-USERS",{{"Last Executed On", each 
if _ = "0" then null else Text.Insert(_,8,"T"), type text}}),

You are right about the added comma. Apologies, I just forgot about it.

[Borghi]

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 · Accepted solution]

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]

It worked!

Thank you so much!