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.

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:

Data with UNIX timestamp as date and time format
Data with UNIX timestamp as date and time format

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])
Add new column as DateTime
Add new column as DateTime

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

New column in datetime format
New column in 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:

Calculation of Timestamp to DateTime in DAX

Denis Selimovic

Denis Selimovic

As a user from the very beginning, Denis Selimovic is passionate about Power BI and everything related to it. In his blog WhatTheFact.bi, he writes about the latest developments in Power BI and provides tips and tricks on the subject. Besides being a Power BI enthusiast, blog author, speaker and senior consultant, he is also co-organizer of the Meetup Power BI User Group Switzerland.

Kommentare:

5 1 vote
Article Rating
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x