Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Seperate date and time

Hi,

 

how do i seperate the date and time from the column ?

I try to delimiter them and i can change the data type of date to date but not the time.

 

Need Help
Tutorial Requests

3 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    Hi Anonymous 

     

    You can try the below.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLQN7DUNzIwtFQwtTK2sDIwUUjUy9WLyVOKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [DateTime = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "DateTime", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Date", "Time"}),
    #"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter",".","",Replacer.ReplaceText,{"Time"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Date", type date}, {"Time", type time}})
in
    #"Changed Type"

     

    Basically, all I've done is added an extra step Replace Values "." with "",  this will allow you to format time correctly.

     

    Best Regards,
    Mariusz

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

    Please feel free to connect with me.
    Mariusz Repczynski


     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi,

       

      Thanks for helping.

       

      I am relatively new in Power BI.

      i paste the entire code into ''Advance editor in power query' and then this happened.

      it did what it suppose to do split date and time but only 1 line remaining in the table