cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ryank Frequent Visitor
Frequent Visitor

DAX to convert Epoch to DateTime

I have just imported a bunch of data with epoch timestamps from the server. 

 

Does anyone have a DAX expression that can convert this into DateTime format for PBI?

 

Example epoch time value: 1486933998060

 

Ryan

11 REPLIES 11
Super User
Super User

Re: DAX to convert Epoch to DateTime

Don't know how to do it in DAX. In Power Query it can be done, but your example looks 1000x too high, so that would become:

#datetime(1970,1,1,0,0,0)+#duration(0,0,0,[Epoch]/1000)

Specializing in Power Query Formula Language (M)
ryank Frequent Visitor
Frequent Visitor

Re: DAX to convert Epoch to DateTime

Thank you, the example includes the time zone which is why it might appear longer. 

 

How do you apply this power query? do you just 'Insert Step After' in the Applied Steps and add the query there?

 

I did this and changed [Epoch] to my column name but it errored out.



= #datetime(1970,1,1,0,0,0)+#duration(0,0,0,[Epoch]/1000)

 

I'm sure thats wrong as I need to include the expression in someother function right?

Super User
Super User

Re: DAX to convert Epoch to DateTime

Example code:

 

let
    Source = #table({"Epoch"},{{1486933998060}}),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each #datetime(1970,1,1,0,0,0)+#duration(0,0,0,[Epoch]/1000))
in
    #"Added Custom"

 

And this video shows how it was created.

Specializing in Power Query Formula Language (M)
ryank Frequent Visitor
Frequent Visitor

Re: DAX to convert Epoch to DateTime

Thanks for the vid Marcel. Thats great. Its all working. 

 

Its going into UTC though. How do I pull the timezone data using the M query?

 

eg check 1486933894289 on http://www.esqsoft.com/javascript_examples/date-to-epoch.htm

GMT+1030 will pop out of the conversion. 

 

Can we produce this in power query too? ie make the column value local not UTC

Super User
Super User

Re: DAX to convert Epoch to DateTime

With me, UTC +01:00 pops out...

 

Power Query has basic functionality to account for timezones.

 

I adjusted the value (...060 --> ...000) and the code so it will make it a UTC datetimezone value instead of a datetime value.

I added 2 options:

 

1. Conversion to local time.

This is dependent on the systems date/time zone, which may be risky if you publish reports to the web and it may run on a server in any timezone.

 

2. Conversion to a fixed UTC offset (i.c. UTC +10:30).

This is suitable if the destination time zone has no Daylight Saving Time switches (or other clock switches due to government decisions). So if you are in the Adelaide time zone (aren't you?), then the clock will switch back at April 1, 4:30 PM UTC to April 2, 2:00 AM (UTC +09:30).

 

If you want to be at the save side, you may consider creating yourself a table like the one in the picture ("Lokaal" is Dutch for "Local") with clock switches and use this to convert UTC times to the time in your time zone.

 

Coincidentally I'm that guy that created such files for all 130 Windows time zones and consolidated those file in 1 file which allows conversion of date/times between 1/1/2000 and 12/31/2017 from any Windows time zone to any other Windows time zone. I used that as an example about some Power Query functionality (combining binaries) in a video.

 

Clock switches Adelaide.png

 

let
    Source = #table({"Epoch"},{{1486933998000}}),
    AddedUTC = Table.AddColumn(Source, "UTC", each #datetimezone(1970,1,1,0,0,0,0,0)+#duration(0,0,0,[Epoch]/1000), type datetimezone),
    #"Duplicated Column" = Table.DuplicateColumn(AddedUTC, "UTC", "Local"),
    UTCToLocal = Table.TransformColumnTypes(#"Duplicated Column",{{"Local", type datetime}}),
    AddedSwitchZone = Table.AddColumn(UTCToLocal, "SwitchZone", each DateTimeZone.SwitchZone([UTC],10,30))
in
    AddedSwitchZone
Specializing in Power Query Formula Language (M)
Highlighted
tuxsax Frequent Visitor
Frequent Visitor

Re: DAX to convert Epoch to DateTime

Hi,

That seems to be working, the problem is I don't really understand the exact steps for applying it to my specific case.
I have the following columns:

 

2017-07-20_1855.png

 
I need the exact query that will translate them to regular date/time columns.

Thanks!

Ziv

tuxsax Frequent Visitor
Frequent Visitor

Re: DAX to convert Epoch to DateTime

No answer? anyone?

I'll really appreciate some help...

tuxsax Frequent Visitor
Frequent Visitor

Re: DAX to convert Epoch to DateTime

Nevermind, I've managed, thanks for your great help!

ddantapally Frequent Visitor
Frequent Visitor

Re: DAX to convert Epoch to DateTime

This logic is not working in my casepowerbi error.JPG