Calculate total minutes (one value for each day)

[mtrevisiol]

Hi everyone, I've got this table:

For each day, I've got the list of operators who worked in each WC (Work Center), their start time anda end time. The column Available minutes is equal to 225 if the date is a Saturday, 450 otherwise. The column Elapsed minutes is given by Datetime End - Datetime Start.

My purpose is to get for each Day, for each WC the available minutes of that day and the total elapsed minutes. The problem is that I would like to have 450 as available minutes of the day, not the sum for each Operator, and the total sum of the Elapsed minutes.

This is the ideal result (referred to the whole table) obtained through a pivoting with Excel:

Here is the xlsx file: https://www.dropbox.com/scl/fi/ujxx5dvq7auw48xqjr0u5/Cartel.xlsx?dl=0&rlkey=ozoej78y0zlj22198t8lsdgl...

Thank you!

[ChrisMendoza · Accepted solution]

@mtrevisiol -

I attempted a solution. Using this as the sample data:

Result:

Basically I used Power Query to:

• Calculate the Duration.TotalMinutes([Datetime stop]-[Datetime start])
• Grouped by [WC] and [Day]
  • SUM of the above calculation
• Added [isSaturday] if Date.DayOfWeek([Day],Day.Monday) = 5 then true else false
• Added your 'minutes available' logic each if [isSaturday] = true then 225 - [Total Minutes Elapsed] else 450 - [Total Minutes Elapsed]
• Removed columns

[mtrevisiol]

Thank you so much for the help @ChrisMendoza