I recently had a dataset in Power BI that contained Unix timestamps that I needed to convert into local time and take into account daylight savings time. I looked around for a bit, but I could find a cut and dry example for a simple way to do this, so I decided to make share my solution.
First I’m going to make a simple data model using R. If you already have data that you are going to apply this to, you can skip this part.
Here is the R code if you wanted to follow along with my example
#Build simple order table
id <- (1:2000)
productId <- round(runif(2000,1,50))
unixTimeStamp <- round(runif(2000,1420070400,1513209599))
orders <-data.frame(id,unixTimeStamp,productId)
write.csv(orders,file = “orders.csv”,row.names = F)
#Build simple products table
product.id <- (1:50)
product.price <- round(runif(50,.50,200),2)
products <- data.frame(product.id,product.price)
write.csv(products,file = “products.csv”,row.names = F)
Alright, now that we have our data we need to upload it to Power BI. We can do so by clicking Get Data from the home menu and clicking Text/CSV and importing your orders and products table.
Once we have those two tables in Power BI, we just need one more table to house our daylight savings time start and end times per year. I constructed this table using this website.
If your years are within the same I needed you can just download my table here.
Now that we have all our data available, we need to a year field in our order table to be able to join on our DST table. In order to do that, I am going to first translate our Unix timestamps to a readable time.
Let’s jump into the Power Query time by hitting Edit Queries.
In our orders table lets select Custom Column from the Add Column tab.
Enter this formula:
#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [unixTimeStamp])
And let’s call it dateUTC and change its type to DateTime.
Then we extract our year from that date by creating a new Custom Column called year using this formula:
Date.Year([dateUTC])
Now we can merge our orders table with the DST table on year by clicking Merge Queries on the home tab in the Query Editor.
And expand them to pull DST_Start and DST_End into our orders table.
From here we can start to add some logic to see if the date of the order is during daylight savings or not.
Add another custom column and use this formula and call it isDST?:
if
[dateUTC]>=[DST_Start]
and
[dateUTC]<[DST_End]
then “true”
else “false”
Finally, we have all the piece together to make our final transformation from Unix time to our local time. I currently work in CST so we are either behind 5 or 6 hours depending on the time of year. You can edit the formula to your timezone.
Add one more last Custom Column named dateLocal and add the formula:
if [#”isDST?”] = “true”
then [dateUTC] + #duration(0,-5,0,0)
else
[dateUTC] + #duration(0,-5,0,0) + #duration(0,-1,0,0)
Now if we go back and do some validation we should see that indeed we are getting the correct local time for our dateLocal field.
There you have it. Close and Apply your changes in the Power Query side, and you’re ready to start analyzing your data confident that your time entries are now in local time.
