Convert UNIX Timestamp to DATE or DATETIME format in Power BI

This post is also available in: Deutsch

If you find a date or datetime format in UNIX format, the first thing you wonder is what it is all about or how you can display it “normally”. This is because the UNIX date format looks like this:

1604135115

What exactly is UNIX Timestamp?

To find a way to convert this, we first need to understand what the number means. The UNIX timestamp is the number of seconds since January 01, 1970. You can read exactly about why it is like this in the Wikipedia article. I want to focus on finding a solution for this problem.

In the above example, 1604135115 seconds after 01 January 1970 is 31 October 2020 at 09:05 AM and 15 seconds.

Update February 20, 2023: The classic UNIX timestamp is, as said, the number of seconds since 01/01/1970. However, there are some programming languages and APIs that specify the timestamp in milliseconds since 01/01/1970. In such a case, the solutions given below must simply be divided by 1000. Some examples of this can be found at the end of the article. The timestamp for a value in 2023 is then not 10-digit as indicated above, but 13-digit. However, this is not a classic UNIX timestamp.

Transformation in Power Query

As a test scenario, we use the following system logs, since UNIX timestamp often appears in logs or similar technical data:

The easiest way to convert the UNIX timestamp is to do exactly what the UNIX timestamp does. We add the specified number of seconds to January 01, 1970. To do this, we create the date 01 January 1970 as a new column using the #datetime function and then add the number of seconds using the #durantion function:

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

The new column contains the specific date and time as datetime format:

The original TimeStamp column can now be deleted if it is not needed in the report. From now on we can continue working with the real date.

Converting UNIX Timestamp with DAX within Power BI

If a conversion to Power Query would not be possible for some reason, then there is still the possibility to do this in DAX. My first option would be to do this as a Calculated Column. However, I would generally do this conversion directly in Power Query, since it is part of the data loading and transformation process.

However, we need one more calculation than in Power Query. Let’s start again by creating the initial date 01 January 1970 with the DATE function:

DATE(1970, 01, 01)

Now we have to add the timestamp seconds. However, this is not possible by simply using the TIME function. We must therefore first calculate the timestamp down to the number of days and the time:

UnixTimestamp = tTimestamp_DAX[TimeStamp] / ( 60 * 60 * 24 ) 

Finally, we need to add to the start value the time duration calculated down to days and time:

DateTime = 
VAR UnixTimestamp = tTimestamp_DAX[TimeStamp] / ( 60 * 60 * 24 ) 
RETURN
DATE( 1970, 01, 01 ) + UnixTimestamp

We can now use the new column in the report. We should hide the timestamp column for a better overview in the frontend:

Modifications for timestamp in millisecond format

As stated in the update above, there are also programming languages that specify the timestemp in milliseconds instead of seconds since 01/01/1970. In this case, the formulas change as follows:

#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [TimeStamp] / 1000 )

Or for the DAX approach:

DateTime = 
VAR UnixTimestamp = ( tTimestamp_DAX[TimeStamp] / 1000 ) / ( 60 * 60 * 24 ) 
RETURN
DATE( 1970, 01, 01 ) + UnixTimestamp