- In Power Query Editor, Select Add Column-> Custom Column to calculate the actual time based on ticket region.
2. Key in the Regional Different Timezone formula as below.
The [Create Date]+#duration(days as number, hours as number, minutes as number, seconds as number) would be the actual time on different region.
Different Timezone =
if ([Region]="China Region") then [Created Date]+#duration(0,12,0,0)
else if ([Region]="India Region") then [Created Date]+#duration(0,9,30,0)
else if ([Region]="Asia Pacific Region") then [Created Date]+#duration(0,12,0,0)
else if ([Region]="EMEA America Region") then [Created Date]+#duration(0,6,0,0)
else [Created Date]
3. Right click the Different Timezone column and select Duplicate Column.
4. Right click the Different Timezone-Copy column and select Transform-> Time only.
5. Rename Different Timezone-Copy to Created Time.
6. In the Home Tab, select Enter Data.
7. To compare the business working hours, create a table name Supporting Time, with the fields of Region, Start Supporting Time and End Supporting Time.
8. Use the “+” sign to insert the column and row.
9.Fill in the Region time with the Region name match with Tickets data.
10. Go to Tickets table, select Merge Queries.
11. Select Supporting Time from the dropdown list.
12. Select Region from both tables to join in Left Outer.
13. Expand the Supporting Time in Tickets table by select Start Supporting Time and End Supporting Time.
14. Add a new Custom Column to create comparison between Created Time and Supporting Time.
Outside Working Hours =
if [Start Support Time]=null then "no"
else (
if [Created Time]<[Start Support Time] or [Created Time]>[End Support Time] then "yes"
else "no")
15. Filter the tickets Outside Working Hours=yes.
16. So now all the Outside Working Hours tickets is filtered out as below.