Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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}}),
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.
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
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}}),
It worked!
Thank you so much!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |