Forum Discussion

thewiseben's avatar
thewiseben
Frequent Visitor
8 years ago

Another convert text string to date/time format

Desktop:

I have a number of text strings that I need to convert to date/times so I can do some comparisons.  Can't find a conversion that works well. 

 

Text is in format: "yyyy-MM-dd-hh.mm.SS.mse000".  I'd be very happy to have it in format: "dd/mm/yyyy hh:mm:ss.mse".  Whatever will allow me to perform calculations on the columns.

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    So, an example of what you have might be:

     

    2018-04-06-15.16.35mse100

     

    for April 6th, 2018 at 3:16:35 PM 100 milliseconds?

    • thewiseben's avatar
      thewiseben
      Frequent Visitor
      8 years ago

      2018-04-08-17.27.46.092000

       

      For April 8th, 17:27:46.092 (with three trailing zeros that the source exports for some reason).

       

      I would love to have this in some format that would be understandable for Power BI.

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        8 years ago

        How about this?

         

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNA1MNE1sNA1NNczMtczMdMzsDQyMDBQio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [String = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"String", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "String", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, true), {"String.1", "String.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"String.1", type date}, {"String.2", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1",".",":",Replacer.ReplaceText,{"String.2"}),
    #"Extracted First Characters" = Table.TransformColumns(#"Replaced Value", {{"String.2", each Text.Start(_, 12), type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Extracted First Characters", "String.2", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, true), {"String.2.1", "String.2.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"String.2.1", type time}, {"String.2.2", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"String.1", "Date"}, {"String.2.1", "Time"}, {"String.2.2", "Milliseconds"}})
in
    #"Renamed Columns"