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.mse...
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"
      • thewiseben's avatar
        thewiseben
        Frequent Visitor
        8 years ago

        Hmm, I couldn't get that to work. 

         

        I ended up using this:

         

            #"Split Column by Position" = Table.SplitColumn(#"Removed Columns", "ServiceItemOpenDateTime", Splitter.SplitTextByPositions({0, 10}, false), {"ServiceItemOpenDateTime.1", "ServiceItemOpenDateTime.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"ServiceItemOpenDateTime.1", type date}, {"ServiceItemOpenDateTime.2", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1","-","",Replacer.ReplaceText,{"ServiceItemOpenDateTime.2"}),
    #"Extracted First Characters" = Table.TransformColumns(#"Replaced Value", {{"ServiceItemOpenDateTime.2", each Text.Start(_, 12), type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted First Characters", "ServiceItemOpenDateTime.2", Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.Csv, true), {"ServiceItemOpenDateTime.2.1", "ServiceItemOpenDateTime.2.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ServiceItemOpenDateTime.2.1", type text}, {"ServiceItemOpenDateTime.2.2", Int64.Type}}),
    #"Replaced Value1" = Table.ReplaceValue(#"Changed Type2",".",":",Replacer.ReplaceText,{"ServiceItemOpenDateTime.2.1"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Replaced Value1", {{"ServiceItemOpenDateTime.2.2", type text}}, "en-US"),{"ServiceItemOpenDateTime.2.1", "ServiceItemOpenDateTime.2.2"},Combiner.CombineTextByDelimiter(".", QuoteStyle.None),"Merged"),
    #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns", {{"ServiceItemOpenDateTime.1", type text}}, "en-US"),{"Merged", "ServiceItemOpenDateTime.1"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged.1"),
    #"Changed Type3" = Table.TransformColumnTypes(#"Merged Columns1",{{"Merged.1", type datetime}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type3",{{"Merged.1", "ServiceItemOpenDateTime"}})
in
    #"Renamed Columns1"

         

        It still doesn't do *quite* what I'm looking for, but it is close enough for government work.