Forum Discussion

jaypatel's avatar
jaypatel
Helper III
6 years ago
Solved

Extract Date and Time from Text


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 

Need Help
  • Mariusz's avatar
    Mariusz
    6 years ago

    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

     

1 Reply

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    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