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
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 mussaenda !
Thank you for your reply and help!
I actually don't know "M" so I couldn't apply the steps but thanks.
I actually created a list of the times, then used "column from example" and created for each hour a correspondeing hour, like 300 in original column then I write 3:00 in the column from example and then just used related table to match them up against the fact table!
Thanks everyone very much!