Forum Discussion
Add colon (:) between string value to convert to time format
Tried to change the format to Date/Time. Error saying 'We couldn't parse the input provided as DateTime value.' shows up.
The column in question is Date TIme. Merged Date and Time columns to create it.
So need to convert 0800 in the first row to 08:00.
Hi Anonymous
You can add a custom column, like below
=Text.Combine({Text.Start([Time], 2), ":", Text.Middle([Time], 2, 2)})Also, see the attached for detail
Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
LinkedIn
2 Replies
Hi Anonymous
You can add a custom column, like below
=Text.Combine({Text.Start([Time], 2), ":", Text.Middle([Time], 2, 2)})Also, see the attached for detail
Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
LinkedInI did this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDXNTDVNTIwMlDSUTKwMDDIKCpWitUBSpghSRgaGyMkjJAlDJB0mOgaG8AlTFB1GMHtsDQxBUvEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Column1", type text}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type","hrs","",Replacer.ReplaceText,{"Column1"}), #"Split Column by Position" = Table.SplitColumn(#"Replaced Value", "Column1", Splitter.SplitTextByPositions({0, 2}, true), {"Column1.1", "Column1.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Column1.1", Int64.Type}, {"Column1.2", Int64.Type}}), #"Added Custom Column" = Table.AddColumn(#"Changed Type1", "Custom", each let splitColumn12 = Splitter.SplitTextByDelimiter(":", QuoteStyle.None)(Text.PadStart(Text.From([Column1.2], "en-US"), 2, ":")) in Text.Combine({Text.From([Date], "en-US"), " ", Text.From([Column1.1], "en-US"), ":", Text.From([Column1.2], "en-US"), splitColumn12{1}?}), type text), #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom Column",{{"Custom", type datetime}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Column1.1", "Column1.2"}) in #"Removed Columns"
