cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nhol
Helper III
Helper III

Seperate Time from Date

I have a [Date] column which includes Date and Time.

I would like to extract the time to have it in a seperate column as well as the date BUT Still keeping the original [Date] column.

I created a copy of the [Date] column and tried to apply a simple format. This worked until I had to use it in an advanced filtering and apparently it still kept the date although it is not shown (because of formatting).

(the table name is MRR_IR_Paper)

 

[Date].PNG

 

 

How it can be done?

 

Thanks!

Nir

 

1 ACCEPTED SOLUTION

Thanks so much!

That helped although I was not able to use it in a visual because it can not accept a measure as Axis.

I think I should go ahead and add another field in the database.

 

Best,

NH

View solution in original post

5 REPLIES 5
stla
Helper I
Helper I

You can go to the Query editor ("Transform data"), then the "Add column" tab, then "Time" in the panel "From date & time":

 

extract_time_compressed.gif

v-qiuyu-msft
Community Support
Community Support

Hi @nhol,

 

You can create a calculated column below: 

Time = FORMAT([Date],"hh:mm:ss")

 

Set the data type of this new column as Time data type: 

 

a1.PNG

 

Best Regards,
Qiuyun Yu

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

Not sure what I'm doing wrong but PowerBI keeps throwing an error message:

Maybe it is something related to the fact I'm on DirectQuery mode, which is must in my case as I need real time data in my model

 

test.png

 

Any other workaround?

 

Thanks!

Nir

v-qiuyu-msft
Community Support
Community Support

Hi @nhol,

 

As you are using DirectQuery mode, please create a measure rather than calculated column. To make sure Format() function works, please enable below option: 

 

q4.PNG

 

Measure = FORMAT(MAX([CreateTime]),"hh:mm:ss")

 

q5.PNG

 

Or, assume you connect to SQL Server database, you can write T-SQL query to get data: 

 

q6.PNG

 

 

Best Regards,
Qiuyun Yu

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

Thanks so much!

That helped although I was not able to use it in a visual because it can not accept a measure as Axis.

I think I should go ahead and add another field in the database.

 

Best,

NH

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.