How to get deadline date excluding weekends and holidays in Power Query

[lennox25]

I have a date table and the result table. I had worked out a Deadline Date using the Date of Arrival and the Deadline Days (i.e 2)

In Power Query (which im still learning) I have this = Table.AddColumn(#"Filtered Rows1", "Deadline Date", each Date.AddDays([Date of Arrival],[Deadline])) 

What do I need to do to exclude the weekends and holidays? One of my rows has 

Date of Arrival = 09/11/23  Deadline Days are 2 (which is a Saturday) the current result is 11/11/2023. I need it to be 13/11/23 (Monday)

[Anonymous]

Hi @lennox25 ,

Please create a new holiday table:

And add a custom column use:

let
    DayList = List.Dates([Date of Arrival], 10 + [Deadline], #duration(1,0,0,0)),
    RemoveWeekends = List.Select(DayList, each Date.DayOfWeek(_, Day.Monday) < 5),
    RemoveHolidays = List.Select(RemoveWeekends, each not List.Contains(Holidays[Date], _)),
    Result = List.FirstN(RemoveHolidays, [Deadline] + 1)
in
    List.Last(Result)

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[lennox25]

Genius! If there is no date of arrival is there any way of it not throwing up an error?

[Anonymous · Accepted solution]

Hi @lennox25 ,

Please try this:

let
    DayList = List.Dates([Date of Arrival], 10 + [Deadline], #duration(1,0,0,0)),
    RemoveWeekends = List.Select(DayList, each Date.DayOfWeek(_, Day.Monday) < 5),
    RemoveHolidays = List.Select(RemoveWeekends, each not List.Contains(Holidays[Date], _)),
    Result = List.FirstN(RemoveHolidays, [Deadline] + 1)
in
    if [Deadline] = null then null else List.Last(Result)

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[lennox25]

Thank you! Amazing! 

[lbendlin]

Use the Power Query Coalesce operator ??

[lbendlin]

Have a calendar table available with these dates clearly marked.  Do a merge with your result table to pull that flag in/to filter on it. Or do an anti join etc.