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 ,
Based on your description, we may achieve your requirement in Query Editor.
More details will help us get the solution more quickly.
For your second requirement, we could replace values first like below.
And then filter rows.
Here is the output.
If it is convenient, could you please share the data sample and your desired output so that we could help further on it.
Best Regards,
Cherry
Hi v-piga-msft !
Thank you for your reply and help.
Unfortunatlly, as I mentioned in the topic that I tried the replace function, but it doesn't work. When I enter "Load and Apply" to close the query editor it shows format.error: can not convert to a number.