Remove duplicates while do time rounding | clean time data

[Aizhanz]

Hello, 

I have an issue with cleaning my time data. I have duplicates as I highlighted in the screenshot. Ex: rider_id=25642, has duplicate with time start:  row5: 24/08/2020 7:31:11 AM and row2: 23/08/2020 7:33:10 AM. How to clean such data? 

I tried to use "Start of Hour", and "End of Hour" to round the time and then to use "remove duplicates". But it doesn't work (I suppose because PBI doesn't see that I rounded time).  

rider_id	actual_start_at	actual_end_at
25642	2020-08-23 03:10:11 UTC	2020-08-23 06:30:10 UTC
25642	2020-08-23 07:33:10 UTC	2020-08-23 11:30:11 UTC
48972	2020-08-24 01:30:10 UTC	2020-08-24 04:38:11 UTC
25642	2020-08-24 03:17:10 UTC	2020-08-24 06:30:10 UTC
25642	2020-08-24 07:31:11 UTC	2020-08-24 11:30:10 UTC

[sanalytics · Accepted solution]

@Aizhanz 

The given data can not be unique as it is in different date.If you convert the whole data based on time.then below are the PQ steps

let
Source = Source,
#"Replaced Value" = Table.ReplaceValue(Source,"UTC","",Replacer.ReplaceText,{"actual_start_at"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","UTC","",Replacer.ReplaceText,{"actual_end_at"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value1", "actual_start_at", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"actual_start_at.1", "actual_start_at.2", "actual_start_at.3"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"rider_id", Int64.Type}, {"actual_start_at.1", type date}, {"actual_start_at.2", type time}, {"actual_start_at.3", type text}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type", "actual_end_at", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"actual_end_at.1", "actual_end_at.2", "actual_end_at.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"actual_end_at.1", type date}, {"actual_end_at.2", type time}, {"actual_end_at.3", type text}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type1",{"rider_id", "actual_start_at.2", "actual_end_at.2"}),
#"Calculated Start of Hour" = Table.TransformColumns(#"Removed Other Columns",{{"actual_start_at.2", Time.StartOfHour, type time}}),
#"Calculated End of Hour" = Table.TransformColumns(#"Calculated Start of Hour",{{"actual_end_at.2", Time.EndOfHour, type time}}),
#"Removed Duplicates" = Table.Distinct(#"Calculated End of Hour")
in
#"Removed Duplicates"

Hope this will help you.

Regards

Sanalytics

[Vijay_A_Verma]

Actually, your dates are different in Start / End of Hours. First one is 23-8-20 and second one is 24-8-20. Hence, this will not be treated as duplicates record. Hence, PQ is behaving in the right manner. But if you want to extract time only to check for duplicates, use below formulas for Start of Hour and End of Hour columns

= Time.From(Time.StartOfHour([actual_start_at]))
= Time.From(Time.EndOfHour([actual_end_at]))