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:
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:
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:
Finally, we need to add to the start value the time duration calculated down to days and time:
We can now use the new column in the report. We should hide the timestamp column for a better overview in the frontend:
