Forum Discussion
Power Query Multiple conditions - SLAs
Hi, apologies, I just realised that. The file should now be accessible.
I will read through your response in the meantime. Thanks
try the solutions first. then let me know if it doesn't work. I will try to look into it
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
NextBusinessDayHI, 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.
Hi, thanks for your efforts so far, much appreciated.
However, I checked the file you shared with me and the summary of what you did (above)
Unfortunately, the logic in the custom column in your file seems to "SLA-Fail" all orders.
Also I don't seem to see any consideration for Public Holidays.
Thanks