Forum Discussion

IamTDR's avatar
IamTDR
Icon for Responsive Resident rankResponsive Resident
6 years ago
Solved

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...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    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,
    Kelly
    Did I answer your question? Mark my post as a solution!
IamTDR's avatar
IamTDR
Icon for Responsive Resident rankResponsive Resident
6 years ago

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?  

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

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.