Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have imported data from mysql database and the time column in in the format of epoch(1546891681) and want to convert into readable date and time format.
Solved! Go to Solution.
FYI - you can do this in calculated columns as above, but you said @Anonymous you were getting this from a MySQL database. That means it should be going through Power Query. Calculated columns should be a last resort for performance reasons. This SQLBI blog walks you through some of that. I always try to go with Power Query transformations before considering calculated columns in DAX.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCreate the following calculated columns and add them to get the desired format
Days='Quotient('Table'[epoch],86400)
Hours = Quotient(MOD('Table'[epoch],86400),3600)
Mins = Quotient(MOD('Table'[epoch],3600),60)
Sec = MOD(MOD('Table'[epoch],3600),60)
Final Column = Date(1970,01,01) + Days + Hours + Mins + Sec
07 Jan 2019 8:08:01 PM is the values for the sample number given by you. @Anonymous If this helps you kindly accept this as solution and give a kudos 😉
FYI - you can do this in calculated columns as above, but you said @Anonymous you were getting this from a MySQL database. That means it should be going through Power Query. Calculated columns should be a last resort for performance reasons. This SQLBI blog walks you through some of that. I always try to go with Power Query transformations before considering calculated columns in DAX.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingUse the #datetime() and #duration() functions in Power Query. Epoch time is the number of seconds from Jan 1, 1970.
Type = #datetime(1970,1,1,0,0,0) + #duration(0,0,0,1546891690) in a new blank query, then convert to a table, then change the data type to DateTime. A full example below.
You can do the same in a custom column by changing the above formula to ... + duration(0,0,0,[epochtimecolumn])
let Source = #datetime(1970,1,1,0,0,0) + #duration(0,0,0,1546891690), #"Converted to Table" = #table(1, {{Source}}), #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type datetime}}) in #"Changed Type"
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingUser | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |