Forum Discussion
data cleaning (time)
Hi All, I faced two issues while I was cleaning my data.. 1. I have a column with time, 4-3 digits, so 12 o'clock looks like 1200 and 3 o'clock looks like this 300, and of course I have another ...
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrDUNzDRNzIwtFTSUTI0MjBQitVBEzUGCcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Time = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Time", Int64.Type}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Time", type text}}),
#"Split Column by Position" = Table.SplitColumn(#"Changed Type1", "Time", Splitter.SplitTextByPositions({0, 2}, true), {"Time.1", "Time.2"}),
#"Merged Columns" = Table.CombineColumns(#"Split Column by Position",{"Time.1", "Time.2"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Time"),
#"Changed Type2" = Table.TransformColumnTypes(#"Merged Columns",{{"Time", type time}}),
#"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type2", {{"Date", type text}, {"Time", type text}}, "en-GB"),{"Date", "Time"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Date and Time"),
#"Changed Type3" = Table.TransformColumnTypes(#"Merged Columns1",{{"Date and Time", type datetime}})
in
#"Changed Type3"Maybe this will help you.
This is before transformation
This is after transformation
Hi Anonymous ,
Have you solved your problem?
If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
If you still need help, please share some data sample and your desired output.
Best Regards,
Cherry