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
Mariusz's avatar
Mariusz
Icon for Community Champion rankCommunity 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