Exclude Holiday in existing Networkday code

[jij19]

Hi all

I have a code where I exclude the weekends to get the networkdays. Can someone help me to additional exclude the holidays which I want to set up in an excel file?

This is my existing code

(StartDate as date, EndDate as date) as number =>
let
ListDates = List.Dates(StartDate, Number.From(EndDate-StartDate), #duration(1, 0, 0, 0)),
RemoveWeekends = List.Select(ListDates, each Date.DayOfWeek(_, Day.Monday) < 5),
CountDays = List.Count(RemoveWeekends)
in
CountDays

Thanks a lot!

[CNENFRNL]

let
ListDates = List.Dates(StartDate, Number.From(EndDate-StartDate) + 1, #duration(1, 0, 0, 0)),
RemoveWeekendsHolidays = List.Select(ListDates, each Date.DayOfWeek(_, Day.Monday) < 5 or not List.Contains(Holidays, _))
CountDays = List.Count(RemoveWeekends)
in
CountDays

(StartDate as date, EndDate as date) as number =>
List.Count(List.Select(List.Dates(StartDate, Number.From(EndDate-StartDate) + 1, #duration(1, 0, 0, 0)), each Date.DayOfWeek(_, Day.Monday) < 5 or not List.Contains(Holidays, _)))

[jij19]

I don't really know how I should put this in my existing code... Could you write it with my existing code

[CNENFRNL · Accepted solution]

(StartDate as date, EndDate as date) as number =>
List.Count(List.Select(List.Dates(StartDate, Number.From(EndDate-StartDate) + 1, #duration(1, 0, 0, 0)), each Date.DayOfWeek(_, Day.Monday) < 5 or not List.Contains(Holidays, _)))

[jij19]

Thanks a lot! Any idea why the data load takes such a long time? is running for 10min and still didn't load the data...And I only have 3 dates to test in the holiday file.

[jij19]

[v-shex-msft]

Hi @jij19,

I'd like to suggest you add buffer functions to these processes to prevent memory leaks and duplicated memory resources spent on these calculations.

Chris Webb's BI Blog: New Options For The Table.Buffer Function In Power Query Chris Webb's BI Blog ...

Table.Buffer - PowerQuery M | Microsoft Docs

List.Buffer - PowerQuery M | Microsoft Docs

Regards,

Xiaoxin Sheng