Extract Date and Time from Text

[jaypatel]

Hi all,

I have one Date as datatype text. I want to extract Date and Time from that text.  My datefield is like "Wed May 13 12:06:29 UTC 2020". I tried to change datatype from Power BI desktop but it shows error and I tried with Text.Middle function but it didn't work for me as well. 
Now, how can I extract date and time from mention text?

Thanks,

Jay 

[Mariusz · Accepted solution]

Hi @jaypatel 

Try the below script or see the attached for the ref.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk9NUfBNrFQwNFYwNLIyMLMyslQIDXFWMDIwMlCKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [DateTimeColumn = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each 
        let split = 
            Text.Split( [DateTimeColumn], " " ),
            dateTime = DateTime.FromText( split{5} & " " & split{1} & " " & split{2} & " " & split{3} )
        in 
            dateTime
    )
in
    #"Added Custom"

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn