Date in different format will not change data type to date, but will change to Date/Time

[Anonymous]

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

[Jimmy801 · Accepted solution]

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
	TransformToDate

Copy 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

[jlockley]

Hi,

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

[Jimmy801]

Hello @jlockley 

i would suggest to create a new post and to specify exactly what you need. You can also specify my name. I would like to help

Jimmy

[v-lid-msft]

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,