Search code examples
powerbipowerquerydata-analysisdata-cleaning

Getting negative value when subtracting two time columns in the PowerQuery

I'm just working on the Google's Data Analytics capstone case study. In the give dataset, I need to find the difference between ride started time and the end time. I got correct values overall except few columns. Below I have attached the screenshot.

I'm cleaning the data using PowerQuery

I have subtracted Started_time and Ended_time i.e. Ended_time - Started_time. Since the ride started at 23:59:44 and ended at 00:05:50, the value I'm getting is -23:53:54 which is in negative and incorrect value. Can someone help me how treat this value to get the correct result?

Solution

  • Assuming this table doesn't have Started/Ended dates:

    = Table.AddColumn(#"Changed Type", "Duration",
    each if [Started_Time] > [Ended_Time] then #time(24,0,0) - [Started_Time] + [Ended_Time] 
    else [Ended_Time] - [Started_Time], type duration)

    And if it does have Started/Ended dates then it becomes easier with:

    = Table.AddColumn(#"Changed Type", "Duration", each ([Ended_Date] & [Ended_Time]) - ([Started_Date] & [Started_Time]), type duration)