Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I currently have a table of stored API data. Below I'm showing one seperate column with a few example rows that contain 13 digit Millisecond timestamps. How would I convert this into a DateTime format? Or just any readable Time or Date format that makes it look nice. Even if it's creating a custom column that transforms the data which I have tried to do but I've had no success.
Any help would be appreciated.
Created |
1592397519540 |
1592401918076 |
1592412872330 |
Solved! Go to Solution.
Hi @Anonymous
Download a sample PBIX file with data and code
Try the measure like this
Measure = DATEVALUE("1970-1-1") + MAX('Table'[Created]) /(60*60*24*1000)
To give this
Regards
Phil
Proud to be a Super User!
Hi @Anonymous
Download a sample PBIX file with data and code
Try the measure like this
Measure = DATEVALUE("1970-1-1") + MAX('Table'[Created]) /(60*60*24*1000)
To give this
Regards
Phil
Proud to be a Super User!
Thank you @PhilipTreacy
This works! I appreciate you going through it in simple detail for me.
Hi @Anonymous
Measures are written in the DAX language and need to be created from within the Data view of PBI Desktop.
From your screenshot it looks like you are writing the DAX in the Power Query editor but this only accepts the M language.
Regards
Phil
Proud to be a Super User!
Thank you @PhilipTreacy , I got confused. I'm currently creating a new measurement however, I can't direct it to just one column.
Measure = DATEVALUE("1970-1-1") + ('Created' /(60*60*24*1000))
On here the 'Created' part is asking for the table instead of the column. How do I make it specific for the 'Created' column?
Hi @Anonymous ,
See a similar thread: https://community.powerbi.com/t5/Desktop/How-Can-I-Convert-Milliseconds-Value-into-Date/td-p/361099
The measure suggested is : Measure = DATEVALUE("1970-1-1") + ('Created' /(60*60*24*1000))
Try and see if it works for you
Hello @PC2790
Thanks for the suggestion, I did already go through that answer and the "Measure" wasn't recognised, neither was "DATEVALUE" when I tried to create a custom column of [Created] data.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |