Convert Unix Timestamp to Datetime in Power Query M

A UNIX timestamp is a number of seconds since 00:00:00 UTC on January 1, 1970. This starting point is called the Unix epoch (or POSIX time, or Unix Time).

Computers often use UNIX timestamps to record exact moments in time. This format avoids complications with time zones. However, UNIX timestamps aren’t easy for people to read. So, they often need to be converted into regular date and time formats for reporting. Let’s see how we can convert them.

If you prefer video, check out the one below:

How to Convert UNIX Timestamp to Date and Time

To convert a Unix time to a human-readable date and time you can use the following formula:

#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, 1732411400) 
// output: #datetime( 2024, 11, 24, 1, 23, 20 )

This formula converts the UNIX timestamp 1732411400 into a datetime value. It works by adding 1732411400 seconds to the epoch date of January 1, 1970. This expression translate to November 24, 2024, at 01:23:20.

Converting a Column of UNIX Timestamps

To apply this conversion to a column containing UNIX timestamps, use the following formula in a custom column:

#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [UNIX Timestamp] )

Replace [UNIX Timestamp] with the name of your column. This formula converts each UNIX timestamp in the column to a datetime value. Below image is the result:

Adjusting for Time Zones

UNIX timestamps are in UTC by default. To convert the datetime to a specific time zone, you can use the DateTimeZone.SwitchZone function.

For example, to adjust for the Netherlands’ summertime (UTC+2) you can use:

DateTimeZone.SwitchZone(
  #datetimezone(1970, 1, 1, 0, 0, 0, 0, 0) + #duration(0, 0, 0, [UNIX Timestamp] ), 
  2, 0
)

This adjusts the time to UTC+2.

Let’s say you have the UNIX timestamp 163508400. Here’s how you convert it:

// Output: #datetimezone( 1975, 3, 8, 13, 0, 0, 2, 0 )
DateTimeZone.SwitchZone(
  #datetimezone(1970, 1, 1, 0, 0, 0, 0, 0) + #duration(0, 0, 0, 163508400 ), 
  2, 0
)

This means the UNIX timestamp 163508400 converts to March 8, 1975, at 1:00 PM, in the Netherlands’ summer time (UTC+2).

How to Convert between DateTime and Unix timestamps?

To convert a datetime value to a Unix timestamp in Power Query M, you can use this formula:

Duration.TotalSeconds( [YourDateTime]  - #datetime(1970, 1, 1, 0, 0, 0) )

This formula subtracts the epoch datetime value (January 1, 1970) from your datetime value and converts the result to seconds using the Duration.TotalSeconds function.

For example, applying this formula to #datetimezone(1975, 3, 8, 13, 0, 0) gives us:

// Output: 163508400
Duration.TotalSeconds(
  #datetime(1975, 3, 8, 11, 0, 0)
    - #datetime(1970, 1, 1, 0, 0, 0)
)

This means the datetime value of March 8, 1975, at 1:00 PM converts to a Unix timestamp of 163508400.

Conclusion

In this article, you learned how to convert a UNIX timestamp into a datetime value using Power Query M. You also discovered how to adjust the datetime to your local time zone. For further reading on date and time manipulations in Power Query, check out these recommended articles:

• Converting Julian Dates to Gregorian Dates
• Formatting dates with Custom Formatting Strings
• Creating a Fiscal Calendar with Fiscal Years and Months

Frequently Asked Questions