Forum Discussion
convert Timestamp
Hi,
I have in my file a colomn with TimeStamps like this.
as you see the date is a mix of 8 digits, 9 digits or 10 digits.
I want try to make colomn`s Date & Time Colomnn
How can i make separate coloms with Date and Time from this?
Frixel Try the following:
Date Column = VAR __Date = SUBSTITUTE(LEFT([Column1],SEARCH(" ",[Column1],,0)),"(","") VAR __FirstHyphen = SEARCH("-",__Date,,0) VAR __SecondHyphen = SEARCH("-",__Date,__FirstHyphen+1,0) VAR __Day = LEFT(__Date,SEARCH("-",__Date,,0)-1) VAR __Month = MID([Column1],__FirstHyphen+2,__SecondHyphen - __FirstHyphen - 1) VAR __Year = RIGHT(__Date,LEN(__Date) - __SecondHyphen) VAR __NewDate = __Month & "-" & __Day & "-" & __Year RETURN __NewDatePBIX is attached below sig. Table (17a)
13 Replies
Frixel IMHO, it's a lightweight to cope with in Power Query as there's a specific date type called datetime to hold such data. Pls try M code below,
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W0jDTNbLUNTIwMlAwsLAyMLIyMdFUitUBShjqmkPFza2MgVKWQPFYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), Trans = Table.TransformColumns(Source, {{"Column1", each Text.Remove(_, {"(", ")"})}}), #"Changed Type" = Table.TransformColumnTypes(Trans,{{"Column1", type datetime}}), Split = Table.TransformColumns(#"Changed Type", {"Column1", each [Date = DateTime.Date(_), Time = DateTime.Time(_)]}), #"Expanded Column1" = Table.ExpandRecordColumn(Split, "Column1", {"Date", "Time"}, {"Date", "Time"}) in #"Expanded Column1"Frixel Replace the ( ) with blank (nothing) and then split on space? Do you want Power Query or DAX?
In DAX, use SUBSTITUTE to remove the ( ) and use something like this to extract Date and Time:
Date Column = LEFT([Timestamp],SEARCH(" ",[Timestamp],,0)) Time Column = RIGHT([Timestamp],LEN([Timestamp]) - SEARCH(" ",[Timestamp],,0))Frixel No, that is Power Query.
Frixel , do you have all date-time in dd-MM format .becasue in that case you need just remove ( and ) and you can get date with this setting
