Converting Unix timestamp from Stripe API to Date/Time

[netwire]

Hi,

Is there a way that I can convert the `created` attribute of a Stripe `charge` object to Date/Time? It's currently in text format as a Unix timestamp, i.e.: 1463948811, see example here https://stripe.com/docs/api#charge_object.

I'm new to PowerBI, not sure how to do this in PowerBI Desktop. I assume I'll need to use the Advanced Editor?

[IvanBond]

Hi,

Just an idea - try a function

https://github.com/IvanBond/pquery/blob/master/Date.EpochToDate.m

Best regards,

Ivan

[Greg_Deckler · Accepted solution]

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.

[jramsey5]

This just came in handy - thanks Greg!

[netwire]

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?

[Greg_Deckler]

@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.

[netwire]

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)?

[Greg_Deckler]

Soooo...that's a very good question. One perspective is that it is always the right choice to do as much in "M" as possible as this is more efficient processing-wise. Another perspective on this is that if you are going to end up having to use DAX, then use all DAX, otherwise you are maintaining code in two different languages. This would be like writing a program half in C# and half in java and half in PHP for example. Nobody wants to maintain that. Yet a third perspective is that DAX has, by far, the lower learning curve, especially if coming from Excel so most people start with DAX and then eventually pick up M and start to do more things in M over time.

My best practice, use what works for you given your comfort level with the language and use the right tool for the job. Pivoting tables, and really complex manipulations of the data, that's a job for "M". Adding a custom column, that depends on the formula. Some things are a cake walk in DAX and next to impossible in "M" and vice versa. Adding a custom measure, that's DAX all day long.

I would recommend reading this:

http://www.powerpivotpro.com/2014/10/5-common-mistakes-made-by-self-taught-dax-students/

[Anonymous]

For people who want to do this same function in M, its answered in other posts, but want to combine both options here

#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [UnixTime])

[V_Kyytsis]

Hello,

I tried running this on a smallish dataset of about 800 rows. It calculates for a while and then claims to run out of memory and suggests using 64bit version. Which is odd as that is what I'm using, but more odd that this simple conversion would be so memory consuming.  

Any ideas?

Ville