Need to Load Time Format data only

[Anonymous]

Hi Everyone,

I am facing an issue while loading the data. In my source, I have data like Resolv time, Response time etc.- But in resolv time data is in time format but the issue is I also have lot of special characters in the column. I need to load only the data which is in time format.

Below is my sample data link.
https://1drv.ms/x/s!AgYvkxQDdPgCbAyLFt2Fddzdx38?e=gy9XAS

In the above data we have columns like dep, time. In time column the data is in different formats like it's in Time format, text data and also it has special characters data. 
So, as per my requirement I need to load the data which is highlighted in yellow color and remaining data should not be loaded.

Can anyone help me on this issue?.

Thank you!

Best Regards,
SW

[tackytechtom]

Hi @Anonymous ,

I am not sure whether this one fulfills your requirement, but you might wanna use power query for this:

1. the data:

2. Change Data Type to time (under the transform ribbon)

3. Right click --> remove errors

Does this help? 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[Anonymous]

Hi @tackytechtom ,

Thanks for the response, I have already tried this method but the issue is in my data I have time like 160(h):12(mm):48(ss)- 160:12:48, 189:24:54. If I convert this time format to duration then I am getting error these values "160:12:48", "189:24:54". But I also need these values for my next calculations because after sort out this issue I will need to convert these time into seconds, minutes and hours as per my requirement.

Best Regards,
SW

[tackytechtom]

Hi @Anonymous ,

How about something like this.

Before:

After:

Here the code that you can use in the advanced editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZU5JDoAgDPwLZxOHAkX6B/UByEF4g/H7YrmYmDSzdQ6Ts4EVCwkwZeqGxKOfmrM25Znv2o4LIFZPThCERklhPL9oKf1D7a7bruwCh8hxhF6QBItqy++CPsqU8gA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [timecolumn = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if Text.Contains([timecolumn], ":") then "yes" else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each [Custom] <> null and [Custom] <> ""),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "timecolumn", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"timecolumn.1", "timecolumn.2", "timecolumn.3"}),
    #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"timecolumn.1", "Hours"}, {"timecolumn.2", "Minutes"}, {"timecolumn.3", "Seconds"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Custom"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Hours", Int64.Type}, {"Minutes", Int64.Type}, {"Seconds", Int64.Type}})
in
    #"Changed Type"

I hope, this might give you some inspiration to get closer to what you want 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[Anonymous]

Hi @tackytechtom,

Thanks for the response, I have implemented the mentioned steps and also I have created a custom column to convert HH:MM:SS into seconds. Now I need to load this data into power BI desktop need to create few other calculations.

I will let you know once it is resolved. 

Thank you!

Best Regards,
SW