Power Query

Epoch dates in Power Query | Excel | Power BI

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:

Is your business ready for automation?

Automate processes with Microsoft Power Platform.

DateTime column

 Convert the date column into a DateTime zone column:

DateTime zone column

Add a calculated column referring to your date column:

Custom DateTime 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:

Epoch value

Add a calculated column referring to your epoch column:

Custom column formula

Formulas to use

Are you ready to discover the joy of automation?

Whether you have a project in mind or just want to know how we can help, we’re happy to have a conversation

				
					 
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.

Author
Power Platform Consultant | Business Process Automation Expert
Microsoft Certified Professional (Power Platform Consultant & Solution Architecht)