Forum Discussion

Sal29's avatar
Sal29
Frequent Visitor
3 years ago

Power Query Multiple conditions - SLAs

Hi, I am faced with a Power Query problem, and I hope someone can support me, here is a link to a sample file of orders and Public Holiday tables from our database connected to a Power Bi Report Pow...
General Comment
Need Help
rubayatyasmin's avatar
rubayatyasmin
Community Champion
3 years ago

HI, Sal29 

 

File is not accessable. Can you see to that? 

 

First try this solution. Create a function that determines Next business day. Example code. 

 

let
NextBusinessDay = (inputDate as date) as date =>
let
nextDay = Date.AddDays(inputDate, 1),
nextWeekday = if Date.DayOfWeek(nextDay, Day.Monday) > 4 then Date.AddDays(nextDay, 7 - Date.DayOfWeek(nextDay, Day.Monday)) else nextDay,
Holidays = Table.Column(#"Public Holidays", "Date"), //replace with your table of public holidays
result = if List.Contains(Holidays, nextWeekday) then @NextBusinessDay(nextWeekday) else nextWeekday
in
result
in
NextBusinessDay

 

 

then in your order table calculate whether each order was processed within the SLA.

 

example code:

 

let
Source = #"Orders", //replace with your table of orders
AddedCustom = Table.AddColumn(Source, "SLA Status", each let
orderCreatedDate = [Order Created Date], //replace with your column names
orderPackedDate = [Order Packed Date],
orderCreatedTime = Time.Hour(orderCreatedDate),
orderCreatedIsBusinessHours = orderCreatedTime >= 7 and orderCreatedTime < 19 and not Date.IsInCurrentWeekend(orderCreatedDate),
deadline = if orderCreatedIsBusinessHours then orderCreatedDate else @NextBusinessDay(orderCreatedDate)
in
if orderPackedDate <= deadline then "SLA-Pass" else "SLA-Fail")
in
AddedCustom

 

 

Here is the documentation on how to create function. https://learn.microsoft.com/en-us/power-query/custom-function

 

if my assistance helped you in any way, hit 👍. 

 

Sal29's avatar
Sal29
Frequent Visitor
3 years ago

Hi, apologies, I just realised that. The file should now be accessible. 

I will read through your response in the meantime. Thanks

  • rubayatyasmin's avatar
    rubayatyasmin
    Community Champion
    3 years ago

    try the solutions first. then let me know if it doesn't work. I will try to look into it

    • Sal29's avatar
      Sal29
      Frequent Visitor
      3 years ago

      unfortunately I'm stuck with the function, here is what I adopted from your code. I have also added the table of all holidays on the main post.

       

      let
      NextBusinessDay = (inputDate as date) as date =>
      let
      nextDay = Date.AddDays(inputDate,1),
      nextWeekday = if Date.DayOfWeek (nextDay,Day.Monday) > 4 then Date.AddDays(nextDay,7 - Date.DayOfWeek(nextDay,Day.Monday)) else nextDay,
      BankHolidays = BankHolidayCalendar (#"Bank holiday","Date"),
      result = if List.Contains (BankHolidays, nextWeekday) then @NextBusinessDay(nextWeekday) else nextWeekday
      in
      result
      in
      NextBusinessDay

       

      • rubayatyasmin's avatar
        rubayatyasmin
        Community Champion
        3 years ago

        HI, Sal29 

         

        I tried in a different and easy way. here is the demo file. File will be automatically deleted after download. I just copied your data. You might need to adjust the query steps with your original data. 

         

        Here is a summery of what I did. 

         

        1. Created name of the day column for both order date and packed date.  "Add Column" > "Date" > "Day" > "Name of Day." Do this for both Order Created Date and Order Packed Date.

         

        2. Extracted hour from the add column> Date> time>hour. for both order and packed date. 

         

        3. Finally, a custom column is created, resulting value is SLA-fail or Pass.