Forum Discussion
Power Query: Date/Time Falls Between Business Hours
Hi
I'm struggling to come up with something in power query that will show the following.
I have a records that have a 'Created At' field, which is a date/time field. I also have a '1st Response Time' field, date/time.
The trouble is I need to tell if this '1st Response Time' field falls within the following windows.
- Monday through Thur between 9am to 3pm, Friday 9am to 10:30am
The goal is to look at the 'Created At' field, and see if the first response is within 2 hours...but need to make sure that the 'Created At' field is within business hours.
I'm really stumped here. Any advice would be appreciated
Thanks in advance
Hi IamTDR ，
First go to query editor> split columns :Split the date/time columns into 2 columns: date and time,as you see below:
Then create a calculated column to get the weekday of the field "create at"：
Weekday = FORMAT(WEEKDAY('Table (2)'[Create at.1],1),"DDDD")Finally create 2 measures as below:
is within 2 hours = var a=DATEDIFF(MAX('Table (2)'[Create at.2]),MAX('Table (2)'[the first response.2]),MINUTE)/60 Return IF(a<=2,1,0)is within business hours = IF(MAX('Table (2)'[Weekday]) in FILTERS('Table'[Weekday ])&&MAX('Table (2)'[Create at.2])>=MAX('Table'[Working hour start ])&&MAX('Table (2)'[the first response.2])<=MAX('Table'[Working hour end]),1,0)And you will see:
For the related .pbix file ,pls click here.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
5 Replies
I believe that the decimal portion of the date/time value is the time part. So, you could remove the integer portion and * by 24 to get the hour.
For day of week: https://docs.microsoft.com/en-us/powerquery-m/date-dayofweek
Is there a way for me to create a field in this table that would later allow me to pivot on whether the 2 hour window has been fulfilled?
Should I be researching DateDiff for this?
Sorry, you asked about Power Query. DATEDIFF is DAX. Personally, Power Query's date time functions are fairly terrible to work with so DAX may not be a bad way to go. Yes, you could use DATEDIFF to get the number of hours between two dates very easily. It will be more of a pain in Power Query.
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
Hi IamTDR ，
First go to query editor> split columns :Split the date/time columns into 2 columns: date and time,as you see below:
Then create a calculated column to get the weekday of the field "create at"：
Weekday = FORMAT(WEEKDAY('Table (2)'[Create at.1],1),"DDDD")Finally create 2 measures as below:
is within 2 hours = var a=DATEDIFF(MAX('Table (2)'[Create at.2]),MAX('Table (2)'[the first response.2]),MINUTE)/60 Return IF(a<=2,1,0)is within business hours = IF(MAX('Table (2)'[Weekday]) in FILTERS('Table'[Weekday ])&&MAX('Table (2)'[Create at.2])>=MAX('Table'[Working hour start ])&&MAX('Table (2)'[the first response.2])<=MAX('Table'[Working hour end]),1,0)And you will see:
For the related .pbix file ,pls click here.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!Thanks so much! This looks like this will fit my need.
Also I appreicate the attached PBIX file as it will help me seee the steps and learn.
Thanks again
