PowerBI is such a feature-rich service that when we come across requirements for functionalities that are not natively available it’s easy to question why there isn’t a simple button for every action we could imagine.
A method of calculating the working hours between two datetime columns and adding the result as a new column is one of these features. This seemingly simple task can be shockingly difficult to implement if you want to do this in PowerQuery(M) and not DAX. Here I have outlined the simple and re-usable method for doing this by creating a ‘NetWorkHours’ custom function.
1. Imagine we have a dataset that has two datetime columns, perhaps we want to understand the amount of actual working time it’s taking to complete a certain type of work, for example how long does a process take to complete a certain task type in Salesforce
2. Firstly, to create a custom function click the blank area in the query list to the left of the query editor and select the option for New Query then finally Blank Query.
3. Paste the function provided below into the text area. Take note of the StartOfWorkingDay and EndOfWorkingDay parameters as these should be configured to suit the business needs. As it is the query assumes a normal working day of 9am to 5pm.
The query takes the InitialDateTime and FinalDateTime as input parameters, it then breaks both of these values into numbers representing the date and time separately. The StartOfWorkingDay and EndOfWorkingDay variables are configured as required for the specific business needs and stored as numbers that represent these times. The ListOfWorkingDays variable is assigned as a list that essentially selects all days excluding day 1 and 7 (Sunday and Saturday).
The logic behind the function uses an ‘if-then-else’ statement to perform different calculations based on the day of the week. It then makes use of the List.Median function to identify whether the StartOfWorkingDay, EndOfWorkingDay or EndTime variable is the median value and subtracts the median value of the StartOfWorkingDay, EndOfWorkingDay and StartTime from this. The M code for this can be found at the bottom of the post for easy copy and pasting.
4. On the ‘Add Column’ tab at the top of the query editor, select the ‘Invoke Custom Function’ option.
5. When the custom function is invoked a window will appear to configure the column being added, here you can name the new column and should specify the columns you want to calculate the working hours between.
6. A new column will be added to your query containing the sum of all working hours that fall within the hours specified on week days. Here we can see that the values presented only take into account the working hours between the two dates with any weekends excluded. It’s also important to note that the data type of the column produced will need to be changed to decimal as a separate transformation step following invocation of the custom function.