Forum Discussion
Convert Ticks timestamp to datetime - Power Query
Sorry for the slow reply and thank you for all of your replies edhans Anonymous. I tried a number of options and the following is how I got it to work:
I created a custom columns and used the following code:
DateTime.FromFileTime([Created At])-#duration(584388,0,0,0)I found this here: Solved: Convert C# date ticks to DateTime - Microsoft Power BI Community
Well...UNIX time has the granularity of seconds. Each UNIX time integer is the number of seconds (or "ticks") since 1/1/1970. Each 86,400 interval is a full day. That's why MOD(UNIX time/86400) will give you the decimal time since last midnight.
Hence
Table.AddColumn(TableName, "New Column", each (DateTime.From([TicksColumn]/86400)) + #datetime(1970,1,1,0,0,0))
I don't think it is unix time. Rogerh said they were ticks. And that isn't the same thing. It is the number of 100 nanosecond increments since the beginning of the 21st century.
Even if it was UNIX time your formula doesn't work. You cannot divide 636905005268967000 by 86400 then use DateTime.From() around that.If it is Unix Time in some way (which I do not think it is as unix time is in seconds), use this article to resolve. Working with Unix Epoch Time In Power BI — ehansalytics
But I think these are ticks, and entirely different from Unix Time seconds. Hence, the solution I provided in the first response, which resolved to April 9, 2018. It seemed to me to be a reasonable answer for data someone would be working on today. But until Rogerh replies, I'm just assuming my formula worked. But my formula did resolve. 😁
