Table of Contents
This blog will show how to work with Epoch format in both seconds and milliseconds in Power Query. You can use the formulas shown in both Excel and Power BI.
From DateTime format to Epoch
We will start with a regular date and we want to convert it to Epoch format:
Convert the date column into a DateTime zone column:
Add a calculated column referring to your date column:
Formulas to use
Seconds:
Number.ToText(Duration.TotalSeconds([Date]-#datetimezone(1970,1,1,0,0,0,0,0)))
Milliseconds:
Number.ToText(Duration.TotalSeconds([Date]-#datetimezone(1970,1,1,0,0,0,0,0))*1000)
Replace [Date] by the column that has your date.
From Epoch to DateTime
We will start with this Epoch value and we want to convert it to DateTime format:
Add a calculated column referring to your epoch column:
Formulas to use
Seconds:
#datetimezone(1970,1,1,0,0,0,0,0) + #duration(0,0,0,Number.From([Epoch]))
Milliseconds:
#datetimezone(1970,1,1,0,0,0,0,0) + #duration(0,0,0,Number.From([Epoch])/1000)
Replace [Epoch] by the column that has your date.
