Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Epoch to Datetime

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...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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 
v-frfei-msft's avatar
v-frfei-msft
Community Support
7 years ago

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's avatar
    Anonymous
    Not applicable
    7 years ago

    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