Forum Discussion
Converting Unix timestamp from Stripe API to Date/Time
Well, if it is a Unix timestamp, then it is the number of seconds from 1/1/1970 (UTC). So something like this calculated column should suffice:
UTCTime = VAR UnixDays = [UnixTime]/(60*60*24) RETURN (DATEVALUE("1/1/1970")+UnixDays)Basically, convert the number of seconds to the number of days and then add it to the date value of 1/1/1970. Essentially, the same thing you would do in Excel.
Well, if it is a Unix timestamp, then it is the number of seconds from 1/1/1970 (UTC). So something like this calculated column should suffice:
UTCTime = VAR UnixDays = [UnixTime]/(60*60*24)
RETURN (DATEVALUE("1/1/1970")+UnixDays)Basically, convert the number of seconds to the number of days and then add it to the date value of 1/1/1970. Essentially, the same thing you would do in Excel.
Thanks Greg_Deckler. I'm new to PowerBI, I just tried to Add Column and entered the formula, substituting [UnixTime] with the actual column name [created], but it doesn't seem to work. I get a EOF error.
I then simply tried `(DATEVALUE("1/1/1970")+[created]/(60*60*24))` but PowerBI complained that DATEVALUE isn't a valid function. Thoughts?
netwire - I am guessing that you are adding your column while in the Query Editor, correct? The Query Editor uses a coding language informally called "M" while what I posted is a language called "DAX". To use DAX, simply import your data. Then, on the lefthand side of the screen in the Desktop, click on the middle icon that looks like a spreadsheet. This is essentially your data model. Click on the table that you just imported and then click the "Modeling" tab in the ribbon and then "New Column" in the ribbon. Then you can enter the DAX I posted.
Thanks Greg_Deckler, this worked wonders. Is there a best practice as to when to use Query Editor (with M) and when to use Data (with DAX)?
This just came in handy - thanks Greg!
