Hello. I have a start time and an end time. I want duration shown in minutes. I added a calculated column called duration: Duration - Duration.TotalMinutes ( EndTime-StartTime). The result look good on the surface but I get negative values when it goes from AM to PM.
Hi @Paul.Gerber. In the 2 cases you noted, the start date/time is after the end date/time. I’d check the data entry and confirm the data before proceeding further.
Greg
@Greg here are the actual snapshots of the data table and the query. I’m still puzzled as to why i get a negative value when going from PM to AM the next day
@Paul.Gerber There is no logic in Start and End time that will tell Power Query that in your data AM comes after PM or vice versa, merge Start Date & Start Time and End Date & End Time and then do the subtraction. Time is just a decimal number so it is like doing 1 - 5 = -4
If you’re looking for a solution from the Power Query perspective then I absolutely agree with our expert @AntrikshSharma that merging the columns is the solution and then adding a separate column in the table that shows the differential time. But merging of columns can also cause an issue while doing the Time intelligence analysis since in that type of cases or scenarios it’s recommended to have date and time in two separate columns.
So couple of months back one of our forum member did posted a similar scenario and below is the link of that post provided for the reference purposes from where you can also download the PBIX file since in the current scenario there isn’t posted one.