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.
Using Copilot Studio to Develop a HR Policy Bot
The next addition to Microsoft’s generative AI and large language model tools is Microsoft Copilot
Apr
Pretty Power BI – Adding GIFs
Good UX design is critical in enabling stakeholders to maximise the key insight that they
Apr
Pareto Charts in Power BI and the DAX behind them
The Pareto principle, commonly referred to as the 80/20 rule, is a concept of prioritisation.
Apr
Databricks: Cluster Configuration
Databricks, a cloud-based platform for data engineering, offers several tools that can be used to
Apr
AI Assistance in Microsoft Fabric
The exponential growth of Large Language Models (LLMs) couples with Microsoft’s close partnership with OpenAI
Apr
10 reasons why it’s worth the effort to understand the value of your data
“If leaders really want to create a data driven culture, the journey starts with them!
Apr
Content Safety in Azure AI Studio
Azure AI Content Safety is a solution designed to identify harmful content, whether generated by
Apr
Model Benchmarks in Azure AI Studio
In the constantly changing field of artificial intelligence (AI) and machine learning (ML), choosing the
Apr