Seperate date and time

[Anonymous]

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.

[Mariusz]

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.

[Anonymous]

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

[v-joesh-msft · Accepted solution]

Hi @Anonymous ,

You can try using the Column From Examples in Query Editor. Enter the relevant data in the format you want, and Column From Examples will automatically match the format you want.

Here is a demo, refer to the APPLIED STEPS:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EeWoyemkIwtDnb1yOsm-Dz8BVW_1U9OKrlBKGOq9L3MfCw?e=7cq0hF

Best Regards,

Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.