Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
netwire
Frequent Visitor

Converting Unix timestamp from Stripe API to Date/Time

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?

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

9 REPLIES 9
IvanBond
Advocate II
Advocate II

Hi,

Just an idea - try a function

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

 

Best regards,

Ivan

Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

This just came in handy - thanks Greg!

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

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/

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors