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.
A Brief Overview of Security in Microsoft Fabric
Where Fabric Sits in the Hierarchy As you are probably aware, Microsoft Fabric is Microsoft’s
Dec
Migration of existing Azure Data Factory Platforms to Fabric
Introduction Fabric is the latest product from Microsoft which was recently launched. It is a
Dec
Databricks Security Analysis Tool
In today’s data-driven world a cutting-edge platform is required that seamlessly integrates with the cloud,
Dec
Building Data Apps with Python’s Streamlit
Off-the-shelf solutions for interactive data app development such as Microsoft Power BI are great –
Nov
Power BI to Power AI – Part 3
This post is the third part of a blog series on the AI features of
Nov
Data Factory in Fabric
As data engineers, we use Azure Data Factory on a daily basis to collect datasets
Nov
Microsoft Fabric Is Generally Available
Microsoft Fabric is now Generally Available What has happened? On the 15th November Microsoft announced
Nov
An Introduction to Semantic Link in Microsoft Fabric
Having previously worked in the water & energy industries, I recently created a utilities PowerBI
Nov