Learn how to determine whether a given time and date falls within or outside specific working hours
The challenge
We have a list of Alarms that is registered in a SharePoint list, and we want to know if the alarm was within or outside working hours which is
Mon-Thu from 07:00 to 15:00
Fri from 07:00 to 12:00
Sat-Sun closed
SharePoint list
First we create a SharePoint list, that we can work with, within Power BI
Power BI solution
We are going to solve the challenge directly inside the Power Query editor, so we don’t have to write any DAX
Time Zone Challenge
I always seem to have a time zone challenge when I am working with time inside Power BI against a SharePoint list. Power BI seems to work with UTC, and since I live in another time zone, my data is always 1 hour behind.
To solve this issue, I am creating a custom column with the following code
DateTimeZone.RemoveZone(DateTimeZone.ToLocal(DateTime.AddZone([Alarmtime],0)))
Use transform to change the type to “time only”
So far my Power Query looks like this
Split date and time
Create a duplicate of the Alarmtime column and adjust it to display only the date.
Splitting the date and time into two separate columns can make data management easier
The code / solution
Add another custom column and use the following code
if (
(
[Alarmtime time] > Time.FromText("07:00:00")
and [Alarmtime time] < Time.FromText("15:00:00")
and (
Date.DayOfWeek([Alarmtime date]) <> 5
and Date.DayOfWeek([Alarmtime date]) <> 6
)
)
or (
Date.DayOfWeek([Alarmtime date]) = 4
and [Alarmtime time] > Time.FromText("07:00:00")
and [Alarmtime time] < Time.FromText("12:00:00")
)
)
then "Within working hours" else "Not working hours"
Here is a printscreen from Power Query where I am adding the custom column
Note that the first day of the week is week day number 0 so Friday is weekday 4
Instead of returning Within working hours and Not working hours you can also use true and false but I think the naming is better for the users when we are creating the visuals in Power BI
Here is our current Power Query setup. Users can now begin filtering to determine if the alarm falls within or outside of working hours
In the picture below you can see the day of the week and also the day number
Now we can create a nice visual for the users who can filter on the working hours value
Note how the working hours around 15:00:00 and 12:00:00 are calculated? You might want to fine tune the code by using =< or write the time as 06:59:59 (I think it is only when working for Space X that 1 second makes a difference)
Not working hours
Within working hours
