Epoch to Datetime

[Anonymous]

Hi All 

Most of this has already been answered by this community, but I have now run out of steam and cannot work out what I am doing wrong 

I have a collumn containing Epoch time stamps

e.g: 1538916520 

I have used the following formula provided by @v-piga-msft  

Timestamp = IF(ISBLANK('Table'[epoc]),BLANK(),'Table'[epoc] / 86400000 + DATE(1970,1,1))

I would expect to see the date returned as 20/08/2019 11:07:23

however I get 18/07/1970 19:28:37 

I have also attempted using a custom collumn with the following 

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

this returns the same value 18/07/1970 19:28:37 

I may be missunderstanding what I am supposed to be doing here, I have not worked with UNIX before so please excuse my ignorance of this.

Any help would be much apprechiated 

Thanks 

Ryan

[v-frfei-msft]

Hi @Anonymous ,

Based on my test in power query, for 1538916520, the result should be  1970/1/18. As i made a test using another one. Everything worked well.

M code for your reference.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ1trA0NDM1MlCKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [epoc = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"epoc", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each #datetime(1970,1,1,0,0,0)+#duration(0,0,0,[epoc]/1000))
in
    #"Added Custom"

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"

[Anonymous · Accepted solution]

Thanks for the reply 

I have managed to get it kinda working with the following 

= VAR conversion = 'Table1'[epochtime]/(60*60*24)

RETURN (DATEVALUE("1/1/1970")+conversion)

only problem is it is now returning 01/01/1970 for all the blank cells but I think I can deal with that for now 

Thanks for your help @v-frfei-msft