Another convert text string to date/time format

[thewiseben]

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.

[Greg_Deckler]

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]

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]

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]

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. 

[thewiseben]

I got code-cocky.

Tried to use my approach on all my string columns. 

As soon as I get to some string columns with blanks, I start getting errors in my "Split by delimiters".  Never noticed this in my original column since every row had a record.  Two of the columns with data about cases closing are where I hit an issue.

Would a find/replace option work well here?  Would I be able to get around this by putting "null" in those blanks?

Specifically getting my problem on the two iterations in bold and underlined below.  ANY ideas would be helpful. 

Spoiler

let

    Source = Csv.Document(File.Contents("C:\temp\PGDPEGA1-R0010.CSV"),[Delimiter=",", Columns=23, Encoding=1252, QuoteStyle=QuoteStyle.None]),

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type number}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", type text}, {"Column23", type text}}),

    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "INTERACTIONTYPE"}, {"Column2", "ServiceItemOpenDateTime"}, {"Column3", "ServiceItemCreator"}, {"Column4", "ServiceItemType"}, {"Column5", "ServiceItemLastUpdateDateTime"}, {"Column6", "ServiceItemLastUpdateOperator"}, {"Column7", "ServiceItemID"}, {"Column8", "UnknownConcat1"}, {"Column9", "ServiceItemCloseDateTime"}, {"Column10", "ServiceItemCloseOperator"}, {"Column11", "ServiceItemStatus"}, {"Column12", "CustomerNPI"}, {"Column13", "UnknownConcat2"}, {"Column14", "InteractionCaseID"}, {"Column15", "InteractionCaseOpenDateTime"}, {"Column16", "InteractionCaseLastUpdateDateT"}, {"Column17", "InteractionCaseLastUpdateOperator"}, {"Column18", "InteractionCaseCloseDateTime"}, {"Column19", "InteractionCaseCloseOperator"}, {"Column20", "InteractionCaseStatus"}, {"Column21", "InteractionCaseCreator"}, {"Column22", "WORKBASKET"}, {"Column23", "AssignedOperator"}}),

    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"AssignedOperator", "UnknownConcat1", "UnknownConcat2"}),

    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"INTERACTIONTYPE", "ServiceItemCreator", "ServiceItemType", "ServiceItemLastUpdateOperator", "ServiceItemID", "ServiceItemCloseOperator", "ServiceItemStatus", "CustomerNPI", "InteractionCaseID", "InteractionCaseLastUpdateOperator", "InteractionCaseCloseOperator", "InteractionCaseStatus", "InteractionCaseCreator", "WORKBASKET", "ServiceItemOpenDateTime", "ServiceItemLastUpdateDateTime", "ServiceItemCloseDateTime", "InteractionCaseOpenDateTime", "InteractionCaseCloseDateTime", "InteractionCaseLastUpdateDateT"}),

    #"Extracted First Characters" = Table.TransformColumns(#"Reordered Columns", {{"InteractionCaseLastUpdateDateT", each Text.Start(_, 23), type text}, {"InteractionCaseCloseDateTime", each Text.Start(_, 23), type text}, {"InteractionCaseOpenDateTime", each Text.Start(_, 23), type text}, {"ServiceItemCloseDateTime", each Text.Start(_, 23), type text}, {"ServiceItemLastUpdateDateTime", each Text.Start(_, 23), type text}, {"ServiceItemOpenDateTime", each Text.Start(_, 23), type text}}),

    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted First Characters", "ServiceItemOpenDateTime", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, true), {"ServiceItemOpenDateTime.1", "ServiceItemOpenDateTime.2"}),

    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ServiceItemOpenDateTime.1", type date}, {"ServiceItemOpenDateTime.2", type text}}),

    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "ServiceItemOpenDateTime.2", Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.Csv, true), {"ServiceItemOpenDateTime.2.1", "ServiceItemOpenDateTime.2.2"}),

    #"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter1",".",":",Replacer.ReplaceText,{"ServiceItemOpenDateTime.2.1"}),

    #"Merged Columns" = Table.CombineColumns(#"Replaced Value",{"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"),{"ServiceItemOpenDateTime.1", "Merged"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"SIOpenDateTime"),

    #"Changed Type2" = Table.TransformColumnTypes(#"Merged Columns1",{{"SIOpenDateTime", type datetime}}),

    #"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type2", "ServiceItemLastUpdateDateTime", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, true), {"ServiceItemLastUpdateDateTime.1", "ServiceItemLastUpdateDateTime.2"}),

    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"ServiceItemLastUpdateDateTime.1", type date}, {"ServiceItemLastUpdateDateTime.2", type text}}),

    #"Split Column by Delimiter3" = Table.SplitColumn(#"Changed Type3", "ServiceItemLastUpdateDateTime.2", Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.Csv, true), {"ServiceItemLastUpdateDateTime.2.1", "ServiceItemLastUpdateDateTime.2.2"}),

    #"Replaced Value1" = Table.ReplaceValue(#"Split Column by Delimiter3",".",":",Replacer.ReplaceText,{"ServiceItemLastUpdateDateTime.2.1"}),

    #"Merged Columns2" = Table.CombineColumns(#"Replaced Value1",{"ServiceItemLastUpdateDateTime.2.1", "ServiceItemLastUpdateDateTime.2.2"},Combiner.CombineTextByDelimiter(".", QuoteStyle.None),"Merged"),

    #"Merged Columns3" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns2", {{"ServiceItemLastUpdateDateTime.1", type text}}, "en-US"),{"ServiceItemLastUpdateDateTime.1", "Merged"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"SILastUpdateDateTime"),

    #"Changed Type4" = Table.TransformColumnTypes(#"Merged Columns3",{{"SILastUpdateDateTime", type datetime}}),

    #"Split Column by Delimiter4" = Table.SplitColumn(#"Changed Type4", "ServiceItemCloseDateTime", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, true), {"ServiceItemCloseDateTime.1", "ServiceItemCloseDateTime.2"}),

    #"Changed Type5" = Table.TransformColumnTypes(#"Split Column by Delimiter4",{{"ServiceItemCloseDateTime.1", type date}, {"ServiceItemCloseDateTime.2", type text}}),

    #"Split Column by Delimiter5" = Table.SplitColumn(#"Changed Type5", "ServiceItemCloseDateTime.2", Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.Csv, true), {"ServiceItemCloseDateTime.2.1", "ServiceItemCloseDateTime.2.2"}),

    #"Replaced Value2" = Table.ReplaceValue(#"Split Column by Delimiter5",".",":",Replacer.ReplaceText,{"ServiceItemCloseDateTime.2.1"}),

    #"Merged Columns4" = Table.CombineColumns(#"Replaced Value2",{"ServiceItemCloseDateTime.2.1", "ServiceItemCloseDateTime.2.2"},Combiner.CombineTextByDelimiter(".", QuoteStyle.None),"Merged"),

    #"Merged Columns5" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns4", {{"ServiceItemCloseDateTime.1", type text}}, "en-US"),{"ServiceItemCloseDateTime.1", "Merged"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"SICloseDateTime"),

    #"Changed Type6" = Table.TransformColumnTypes(#"Merged Columns5",{{"SICloseDateTime", type datetime}}),

    #"Split Column by Delimiter6" = Table.SplitColumn(#"Changed Type6", "InteractionCaseOpenDateTime", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, true), {"InteractionCaseOpenDateTime.1", "InteractionCaseOpenDateTime.2"}),

    #"Changed Type7" = Table.TransformColumnTypes(#"Split Column by Delimiter6",{{"InteractionCaseOpenDateTime.1", type date}, {"InteractionCaseOpenDateTime.2", type text}}),

    #"Split Column by Delimiter7" = Table.SplitColumn(#"Changed Type7", "InteractionCaseOpenDateTime.2", Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.Csv, true), {"InteractionCaseOpenDateTime.2.1", "InteractionCaseOpenDateTime.2.2"}),

    #"Replaced Value3" = Table.ReplaceValue(#"Split Column by Delimiter7",".",":",Replacer.ReplaceText,{"InteractionCaseOpenDateTime.2.1"}),

    #"Merged Columns6" = Table.CombineColumns(#"Replaced Value3",{"InteractionCaseOpenDateTime.2.1", "InteractionCaseOpenDateTime.2.2"},Combiner.CombineTextByDelimiter(".", QuoteStyle.None),"Merged"),

    #"Merged Columns7" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns6", {{"InteractionCaseOpenDateTime.1", type text}}, "en-US"),{"InteractionCaseOpenDateTime.1", "Merged"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"InteractionCaseOpenDateTime"),

    #"Changed Type8" = Table.TransformColumnTypes(#"Merged Columns7",{{"InteractionCaseOpenDateTime", type datetime}}),

    #"Split Column by Delimiter8" = Table.SplitColumn(#"Changed Type8", "InteractionCaseCloseDateTime", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, true), {"InteractionCaseCloseDateTime.1", "InteractionCaseCloseDateTime.2"}),

    #"Changed Type9" = Table.TransformColumnTypes(#"Split Column by Delimiter8",{{"InteractionCaseCloseDateTime.1", type date}, {"InteractionCaseCloseDateTime.2", type text}}),

    #"Split Column by Delimiter9" = Table.SplitColumn(#"Changed Type9", "InteractionCaseCloseDateTime.2", Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.Csv, true), {"InteractionCaseCloseDateTime.2.1", "InteractionCaseCloseDateTime.2.2"}),

    #"Replaced Value4" = Table.ReplaceValue(#"Split Column by Delimiter9",".",":",Replacer.ReplaceText,{"InteractionCaseCloseDateTime.2.1"}),

    #"Merged Columns8" = Table.CombineColumns(#"Replaced Value4",{"InteractionCaseCloseDateTime.2.1", "InteractionCaseCloseDateTime.2.2"},Combiner.CombineTextByDelimiter(".", QuoteStyle.None),"Merged"),

    #"Merged Columns9" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns8", {{"InteractionCaseCloseDateTime.1", type text}}, "en-US"),{"InteractionCaseCloseDateTime.1", "Merged"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"InteractionCaseCloseDateTime"),

    #"Split Column by Delimiter10" = Table.SplitColumn(#"Merged Columns9", "InteractionCaseLastUpdateDateT", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, true), {"InteractionCaseLastUpdateDateT.1", "InteractionCaseLastUpdateDateT.2"}),

    #"Changed Type10" = Table.TransformColumnTypes(#"Split Column by Delimiter10",{{"InteractionCaseLastUpdateDateT.1", type date}, {"InteractionCaseLastUpdateDateT.2", type text}}),

    #"Split Column by Delimiter11" = Table.SplitColumn(#"Changed Type10", "InteractionCaseLastUpdateDateT.2", Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.Csv, true), {"InteractionCaseLastUpdateDateT.2.1", "InteractionCaseLastUpdateDateT.2.2"}),

   #"Replaced Value5" = Table.ReplaceValue(#"Split Column by Delimiter11",".",":",Replacer.ReplaceText,{"InteractionCaseLastUpdateDateT.2.1"}),

    #"Merged Columns10" = Table.CombineColumns(#"Replaced Value5",{"InteractionCaseLastUpdateDateT.2.1", "InteractionCaseLastUpdateDateT.2.2"},Combiner.CombineTextByDelimiter(".", QuoteStyle.None),"Merged"),

    #"Merged Columns11" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns10", {{"InteractionCaseLastUpdateDateT.1", type text}}, "en-US"),{"InteractionCaseLastUpdateDateT.1", "Merged"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"InteractionCaseLastUpdateDateTime"),

    #"Changed Type11" = Table.TransformColumnTypes(#"Merged Columns11",{{"InteractionCaseCloseDateTime", type datetime}, {"InteractionCaseLastUpdateDateTime", type datetime}})

in

    #"Changed Type11"