Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Separate Date and Time From Timestamp in Calculated Column

I apologize in advance if this question has been aked before, but I've looked around and can't find the anwser that quite addresses my issue... I have a table in a tabula model with several datetime...
Need Help
PhilipTreacy's avatar
PhilipTreacy
Super User
5 years ago

Hi Anonymous 

Sample PBIX file here.

This will give you the Date column

 

Date = LEFT('Table'[DepartureDateTime], 10)

 

This will give you the Time column 

 

Time = MID('Table'[DepartureDateTime], 12, LEN('Table'[DepartureDateTime] - 12))

 

 To give

Modify the format to suit.

Regards

Phil

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Thanks, Philip fr the reply!

In looking at your solution, and after trying a couple of things, I realized the adding of hte columns in the tble definition as proposed earlier, is probably going to be the best option.

yours works great, but the LEFT portion won't work since our dates don't force an extra 0 (zero) for single days/months. So for example if the datetime string is 

7/6/2019 3:43:00 PM

, your calc returns 7/6/2019 3.

And the date and time columns are going to be used as foreign keys in a tabular model, so my solution I tried didn't work as I just fomatted the full datetime column as a date, time, etc. So it wasn't actually a date value, so the relatinship didn't work.

So, given the original reply and their solution, how would you wedge that into the following table definition:

 

Source = #"SQL/azsds001d database windows net;EDW",
ops_DimFlightSchedule = Source{[Schema="ops",Item="DimFlightSchedule"]}[Data],
#"Filtered Rows" = Table.SelectRows(ops_DimFlightSchedule, each ([Cancelled] <> 2))
in
#"Filtered Rows"

 

And I should correct myself!!! I need to edit the M expression that my table is based on! It's not DAX...sorry about the miscommunication!!