Forum Discussion

Frixel's avatar
Frixel
Post Prodigy
5 years ago
Solved

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 c...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    5 years ago

    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
    __NewDate

     

     PBIX is attached below sig. Table (17a)

Greg_Deckler's avatar
Greg_Deckler
Community Champion
5 years ago

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))
  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 years ago

    Frixel No, that is Power Query. 

    • Frixel's avatar
      Frixel
      Post Prodigy
      5 years ago

      Greg_Deckler 

      The (  ) are still standing there. 

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        5 years ago

        Frixel OK, if you are doing it via DAX, then it would be:

         

        Date Column = 
  SUBSTITUTE(LEFT([Timestamp],SEARCH(" ",[Timestamp],,0)),"(","")

Time Column = 
  SUBSTITUTE(RIGHT([Timestamp],LEN([Timestamp]) - SEARCH(" ",[Timestamp],,0)),")","")

         

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 years ago

    Frixel In Power Query, do this:

    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]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","(","",Replacer.ReplaceText,{"Column1"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",")","",Replacer.ReplaceText,{"Column1"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value1", "Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type date}, {"Column1.2", type time}})
in
    #"Changed Type1"