data cleaning (time)

[Anonymous]

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 column with dates. I want to merge the the two tables at the end and it would look like this 12-april-2018 12:00
I tried split the charachters and rejoin them but it is kinda a mess. Do you have any suggestions on how to transform and clean this data?

2. I have columns where the value is (-) and I want to delete these rows which contains the (-). I tried replacing it with blanks, and then delete the blanks. but it didn't work. 

help please! thanks!

[v-piga-msft]

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

[mussaenda · Accepted solution]

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

[Anonymous]

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!

[v-piga-msft]

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

[Anonymous]

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.