Forum Discussion
Date in different format will not change data type to date, but will change to Date/Time
This is just weird...
The date format in my source is 15-09-2019 10:19:50. (as text) If I try to change the format to Date, it fails, being unable to parse in input as a date.
However if I select Date/Time, it can parse that. Then if I duplicate that column and then change the format of the new column to Date, it works. Why can Date/Time parse it but Date can't??
Hello Anonymous
just apply a DateTime.FromText with culture de-DE and afterwards convert it to date with Date.From
here the complete solution
let Source = #table ( {"DateTime"}, { {"15-09-2019 10:19:50"} } ), TransformToDate = Table.TransformColumns ( Source, { { "DateTime", (datetimei)=> Date.From(DateTime.FromText(datetimei,"de-DE")), type date } } ) in TransformToDateCopy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
4 Replies
v-lid-msftCommunity Support
Hi Anonymous ,
We think it because we can convert datetime to date because it is is similar type, it just need to drop the time part, but the text to date is convert function, we cannot convert a text with time function to date type. But actually we did not need to create a new column, we can archieve your requirement by convert it twice:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTVNbDUNTIwtFQwNLAytLQyNVCKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}},"es-ve"), #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Date", type date}}) in #"Changed Type1"Or we can split it into date part and time part:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTVNbDUNTIwtFQwNLAytLQyNVCKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Date", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Date.1", "Date.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Date.1", type date}, {"Date.2", type time}},"es-ev") in #"Changed Type1"
Best regards,
Jimmy801Community Champion
Hello Anonymous
just apply a DateTime.FromText with culture de-DE and afterwards convert it to date with Date.From
here the complete solution
let Source = #table ( {"DateTime"}, { {"15-09-2019 10:19:50"} } ), TransformToDate = Table.TransformColumns ( Source, { { "DateTime", (datetimei)=> Date.From(DateTime.FromText(datetimei,"de-DE")), type date } } ) in TransformToDateCopy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
JimmyHi,
I have imported times from a database and they have pulled through as text. I need to be able to sum the times in the columns.
I cannot convert back to times as some of the times are over 24hrs.
Is there a way to convert time shown as text back to time and also include time over 24 hours?
I hope that makes sence?
Thanks
Jaimie
