Forum Discussion
Converting Text into Date
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}}),
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"
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
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.