Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

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.

 

Capture.JPG

1 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.

21.PNG22.PNG23.PNG

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.

 

View solution in original post

3 REPLIES 3
Mariusz
Community Champion
Community Champion

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
Not applicable

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

 

Capture2.JPGCapture3.JPG

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.

21.PNG22.PNG23.PNG

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.

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.