Seperate Time from Date

[nhol]

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)

How it can be done?

Thanks!

Nir

[Anonymous]

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

[v-qiuyu-msft]

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: 

Best Regards,
Qiuyun Yu

[nhol]

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

Any other workaround?

Thanks!

Nir

[v-qiuyu-msft]

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: 

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

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

Best Regards,
Qiuyun Yu

[nhol · 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