Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I've imported a MySQL database from a PHP based CMS.
The dates in the database are formatted as integers.
For example:
2016-01-29 20:02 corresponds to:
1454097752 |
And:
2015-11-09 16:45 corresponds to:
1447087522 |
Now, I have the whole data in Power Pivot and I need to work with readable dates and I have no idea how to get them from integers as above.
Does anyone know of a conversion function I could use?
Thank you so much for your help!
This seems to be seconds since 1970.
So you can convert by adding a custom column with this formula
([NumberField]/24/60/60)+25569
This should return a number that you can then convert to Datetime.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi,
It should be something like that, yes.
For 1481574249 which is 2016-12-12 20:24
The formula
([NumberField]/24/60/60)+25569
Returns:
42716,8501014
And applying DATEVALUE to that number returns the error:
"Type mismatch"
PS: The field is set to Auto (Date)
Hi,
I did this as a column-operation in the UI.
But if you want to do it via a formula, try Date.From(..) instead.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
I'm using a calculated column in Power Pivot.
=DATEVALUE([Date Column]/24/60/60+25569)
Wrong forum 😉
You can also apply a column operation there: Home -> Format -> Data Type: Date
Or again in a formula:
FORMAT([Spalte2]; "General Date")
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
User | Count |
---|---|
106 | |
89 | |
81 | |
76 | |
73 |
User | Count |
---|---|
112 | |
104 | |
96 | |
74 | |
66 |