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
