Converting Non Business Days and Hours to Business Days and Hours Power Query

[Technowolf]

Hi

I Have Cases are Created by Customers duing Non Business Hours, When The Agents login during business hours they work on case.

I need to Calculate the TAT. From the Case Created Date - Case Touched by Agent.

Business Hours Start 8:30 AM
Business Hours End 6:00 PM

The Problem is that the Customers can Create cases any time. So cases can be created before or after the Business hours. We can't penalized the Agents since the cases came before they loggin and have a higher TAT.

I want to convert all the case Cases to which came in during the non business Hours to be converted to business hours..

Created Date to Date Modified

I have Come Conditions.below.

If The Created before 8:30 AM the same Day . The Date Modified should be change to the same date 8:30 AM
If The Created After 6:00 PM . The Date Modified should be change to the Next date 8:30 AM
If a Case is Created After 6PM Friday then the Date modified should be changed to Monday 8:30AM
If Case is Created on a Weekend (SAT & SUN), Then the Date Modified Should be Monday 8:30AM

My Sample data

Case No	Created On	Created WeeK Day	Date Moddified	Modified Week Day
1	1/1/2019 9:22	2	1/1/2019 9:22	2
2	1/1/2019 14:10	2	1/1/2019 14:10	2
3	1/1/2019 19:11	2	1/2/2019 8:30	3
4	1/1/2020 6:26	3	1/1/2020 8:30	3
5	11/2/2018 9:21	5	11/5/2018 8:30	1
6	10/26/2019 23:22	6	10/28/2019 8:30	1
7	10/27/2019 23:22	7	10/28/2019 8:20	1
8	10/28/2019 5:53	1	10/28/2019 5:53	1
9	10/28/2019 7:08	1	10/28/2019 8:30	1
10	10/28/2019 18:51	1	10/29/2019 8:30	2
11	10/28/2019 22:34	1	10/29/2019 8:30	2

I Would like to have a power query  Calculated Column, I had  this Dax but my Later steps in my report is not working

I did try to convert weekend to weekdays  need.

if [CET_CreatedWeekDay] = 6 then Date.AddDays([CET_CreatedDateTime],2) else if [CET_CreatedWeekDay] = 7 then Date.AddDays([CET_CreatedDateTime],1) else [CET_CreatedDateTime]

I have this working in Dax  but need this is power Query.

Date Modified =
VAR CreatedTime =
    TIME ( HOUR ( 'Table'[Created On] ), MINUTE ( 'Table'[Created On] ), SECOND ( 'Table'[Created On] ) )
VAR CreatedDate = 'Table'[Created On].[Date]
VAR BusinessStart =
    TIME ( 8, 30, 0 )
VAR BusinessEnd =
    TIME ( 18, 0, 0 )
RETURN
    SWITCH (
        TRUE (),
        'Table'[WeeK Day] IN { 1, 2, 3, 4 }, SWITCH (
            TRUE (),
            CreatedTime >= BusinessStart
                && CreatedTime <= BusinessEnd, CreatedDate & " " & CreatedTime,
            CreatedTime < BusinessStart, CreatedDate & " " & BusinessStart,
            CreatedTime > BusinessEnd, DATEADD ( 'Table'[Created On].[Date], 1, DAY ) & " " & BusinessStart
        ),
        'Table'[WeeK Day] = 5, SWITCH (
            TRUE (),
            CreatedTime >= BusinessStart
                && CreatedTime <= BusinessEnd, CreatedDate & " " & CreatedTime,
            CreatedTime < BusinessStart, CreatedDate & " " & BusinessStart,
            CreatedTime > BusinessEnd, DATEADD ( 'Table'[Created On].[Date], 3, DAY ) & " " & BusinessStart
        ),
        'Table'[WeeK Day] = 6, DATEADD ( 'Table'[Created On].[Date], 2, DAY ) & " " & BusinessStart,
        'Table'[WeeK Day] = 7, DATEADD ( 'Table'[Created On].[Date], 1, DAY ) & " " & BusinessStart
    )

https://community.powerbi.com/t5/Desktop/Converting-Non-Business-Hours-to-Business-Hours/m-p/1004178...

Thanks in Advanced.

Regards,

Charles Thangaraj

[AlB]

Hi @Technowolf 

Try this based on your example. It would probably be more convenient to have the code in the custome column a s a function. You can create the function from it if you prefer.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc7LDcQwCEXRViLWkczDP0wrlvtvIzFOovFsz0VA7wQ6CQFBGO1oJkLj7CS/imRg57hxM8A5fSx8FJPimqfeOKd1rl7DZTIHKWuLxPdmfUL9D/oEXSFbju5t92qs7vezW4BaXreBvYhYTDTGBQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Case No" = _t, #"Created on" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Created on", type datetime}}),

    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom.1", each let dayWeek_=Date.DayOfWeek([Created on],Day.Monday)+1, time_=Time.From([Created on]), date00_ = DateTime.From(DateTime.Date([Created on])), res_= if dayWeek_ = 6 or dayWeek_ = 7 or (dayWeek_ = 5 and time_ > #time(18,0,0)) then date00_ + #duration(8-dayWeek_,8,30,0) else if time_ < #time(8,30,0) then date00_ + #duration(0,8,30,0) else if time_ > #time(18,0,0) then date00_ + #duration(1,8,30,0) else [Created on] in res_, type datetime)
in
    #"Added Custom1"

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Technowolf]

Hi

Can you help me to create the function my acutual columns name are CET_CreatedDateTime and Case Id. 

I really appreciate the help. 

this is the part I dont understand.

 Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc7LDcQwCEXRViLWkczDP0wrlvtvIzFOovFsz0VA7wQ6CQFBGO1oJkLj7CS/imRg57hxM8A5fSx8FJPimqfeOKd1rl7DZTIHKWuLxPdmfUL9D/oEXSFbju5t92qs7vezW4BaXreBvYhYTDTGBQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Case No" = _t, #"Created on" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Created on", type datetime}}),

Thanks in advance

Regards,

Charles Thangaraj

[AlB · Accepted solution]

@Technowolf 

Sure, I can help you with the function if you help me with some Kudos along the way, deal?

1. Create a blank query

2. Open it in the advanced editor

3. Delete all the text and copy this code:

(inputDateT_)=>

let 
    dayWeek_=Date.DayOfWeek(inputDateT_,Day.Monday)+1, time_=Time.From(inputDateT_), 
    date00_ = DateTime.From(DateTime.Date(inputDateT_)),
    res_= if dayWeek_ = 6 or dayWeek_ = 7 or (dayWeek_ = 5 and time_ > #time(18,0,0)) 
            then date00_ + #duration(8-dayWeek_,8,30,0) 
                else if time_ < #time(8,30,0) then date00_ + #duration(0,8,30,0) 
                    else if time_ > #time(18,0,0) then date00_ + #duration(1,8,30,0) 
                    else inputDateT_
in res_

 4. Name the query with the name you want for the function, for instance "convertToBusinessHours". Now you have the function

 5. In your table, add a custom column with the following code:

= convertToBusinessHours([CET_CreatedDateTime])

Note if you choose another function name you have to update the code above with that name

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Technowolf]

Hi

It worked like a charm, Thanks a Ton

Regards,

Charles Thangaraj

[AlB]

@Technowolf 

The piece you refer too (below) is not very important. It is just a piece of code created by the GUI when you enter data manually. Try it: go to "Enter data", enter some data, press Load and you'll see a query gets created with a similar piece of code in the first step. It is the info of the table you've created manually compressed 

=
Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc7LDcQwCEXRViLWkczDP0wrlvtvIzFOovFsz0VA7wQ6CQFBGO1oJkLj7CS/imRg57hxM8A5fSx8FJPimqfeOKd1rl7DZTIHKWuLxPdmfUL9D/oEXSFbju5t92qs7vezW4BaXreBvYhYTDTGBQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Case No" = _t, #"Created on" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Created on", type datetime}}),

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

@Technowolf 

If you create another blank query, and enter the code below (same way as when we created the function), you'll see the first step is the creation of your base table (I copied directly what you posted on "Enter data") then a data type change in the second step and a third step in which the custom column is created by using the function.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc7LDcQwCEXRViLWkczDP0wrlvtvIzFOovFsz0VA7wQ6CQFBGO1oJkLj7CS/imRg57hxM8A5fSx8FJPimqfeOKd1rl7DZTIHKWuLxPdmfUL9D/oEXSFbju5t92qs7vezW4BaXreBvYhYTDTGBQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Case Id" = _t, CET_CreatedDateTime = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CET_CreatedDateTime", type datetime}}),

    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom.1", each convertToBusinessHours([CET_CreatedDateTime]))
in
    #"Added Custom1"

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers