NOOB: How to edit a query to return timestamp in date-time format

[txross]

Hi everyone,

I am extremely new to PowerBi and have a specific question on how to transform my data. I have unix tiemstamps that I want to display as date times. I know I can use this function:

UTCTime = VAR UnixDays = [UnixTime]/(60*60*24)
RETURN (DATEVALUE("1/1/1970")+UnixDays)

But I cannot figure out where to put this. Does it go in a calculated column? If so, do I have to create this as a UDF first to call it in the calculated column? Where do I create a UDF? 

The data in question that I want to transform is being pulled from our Hortonworks Hadoop cluster via the Spark (beta) connector in DirectQuery mode, if that helps. I'm not sure whether it's best to transform the hive query itself or let PowerBI add the cacluated column. 

Sample data:

What I want to do:

UTCTime = VAR UnixDays = [ts]/(60*60*24)
RETURN (DATEVALUE("1/1/1970")+UnixDays)

[alanhodgson · Accepted solution]

Hey @txross,

Looks like you got the formula from the post here. 

You can use it in PBI Desktop, after loading the data from the Query Editor, by going to the "Data" section (spreadsheet icon on the left side), selecting the table on the right side that you want to add the colum to, and navigating to the "Modeling" ribbon on top. There you will click "New Column" and add the formula below:

UTCTime = VAR UnixDays = [UnixTime]/(60*60*24)
RETURN (DATEVALUE("1/1/1970")+UnixDays)

Hope this helps,

Alan