Text into data

[arhetyp]

Hi,

I would greatly appreciate you support on that:

Have extracted from text the fragment which referes to date but off course still remains in TEXT format as below:

I need to convert it into date/time format.

It is in polish but would be the same as 26 September 2018 13:04.

Additional chalenge is some raws are not date-based (the 3rd one).

Regards,

Arek

[v-shex-msft · Accepted solution]

Hi @arhetyp,

I'd like to suggest you do replace value operate in query edit to replace specific value to null, then you can simply use 'Changed Type with Locale' option to change type.

Steps:

Full query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjJTKC+qSj06Oy8zUcHIzNBCwdDYysBEQSlWByhpiixpAJE0NoRI5isUpRYX5OcVpxJWi1XSELctRlZGuHUaWhlYEOsEAysjS1wGGVhaGRnjkTSE6TTEMNbEysBSKTYWAA==", 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}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","o response",null,Replacer.ReplaceValue,{"Date"}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Replaced Value", {{"Date", type datetime}}, "pl-PL")
in
    #"Changed Type with Locale"

Regards,

Xiaoxin Sheng