Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Technowolf
Helper II
Helper II

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

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 NoCreated On      Created WeeK DayDate ModdifiedModified Week Day
11/1/2019 9:2221/1/2019 9:222
21/1/2019 14:1021/1/2019 14:102
31/1/2019 19:1121/2/2019 8:303
41/1/2020 6:2631/1/2020 8:303
511/2/2018 9:21511/5/2018 8:301
610/26/2019 23:22610/28/2019 8:301
710/27/2019 23:22710/28/2019 8:201
810/28/2019 5:53110/28/2019 5:531
910/28/2019 7:08110/28/2019 8:301
1010/28/2019 18:51110/29/2019 8:302
1110/28/2019 22:34110/29/2019 8:302

 

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

 

1 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 

SU18_powerbi_badge

   

View solution in original post

6 REPLIES 6
AlB
Super User
Super User

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 

SU18_powerbi_badge

 

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

 

@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 

SU18_powerbi_badge

   

Hi

 

It worked like a charm, Thanks a Ton

 

Regards,

Charles Thangaraj

@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 

SU18_powerbi_badge

@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 

SU18_powerbi_badge

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors