Power Query has number of Date and Time functions that convert values to date and time. However I haven’t found a function that converts a timestamp value. Fortunately it is easy to calculate a date time from a timestamp value. In this post I’ll explain an easy way of converting timestamp to date time. To Learn more about Power Query read Power BI online book; from Rookie to Rock Star.
What is Timestamp
Timestamp is a whole number value, which is number of seconds from date 1970-01-01 00:00:00. For example; timestamp 100 means 1970-01-01 00:01:40, or timestamp 86400 means 1970-01-02 00:00:00. So the calculation is easy; We have to add timestamp as seconds to the date/time 1970-01-01 00:00:00.
Power Query Convert Timestamp to Date Time
Once we know what is definition of timestamp, and how to calculate date/time from it; easily we can use #duration(0,0,0,<timestamp value>) to show duration in seconds, and add it to the #datetime(1970,1,1,0,0,0) which is date time 1970-01-01 00:00:00.
So as a result, here is the code to convert timestamp to date time;
above query will respond 11/11/2014 9:12:45 AM.
Please note that you have to replace the 1415697165 number with the field name containing timestamp values, or with your static timestamp value in the query.