Returning T/F if a date from a column in another query falls within a Start/End Date

[BradP]

Hi,

I have 2 Power Queries in Excel, one is MainQuery and the other HolidayTable with a column HolidayDate.  MainQuery has  CreateDate and ClosedDate columns.  I want to return a value to say a holiday date falls within the CreateDate and ClosedDate. I mangled a function I found on the community but it doesn't work as expected. Any suggestions would be appreciated!

let func =
(StartDate as date, EndDate as date, HolidayCheck as number, optional Holidays as list, optional StartOfWeek as number) =>
let
startOfWeek = if StartOfWeek = null then 1 else StartOfWeek,

ListOfDates = List.Dates(StartDate,Number.From(EndDate-StartDate),#duration(1,0,0,0)),

CountHolidays = if Holidays = null then ListOfDates else List.Difference(ListOfDates, Holidays),

DeleteWeekends = List.Select(CountHolidays, each Date.DayOfWeek(_, startOfWeek) < 1 ),

CountDays = if List.Count(DeleteWeekends) * 9 >= 9 or HolidayCheck >= 9 then 9 else 0

in
CountDays ,

[BradP]

Thank-you for that!

[m_dekorte · Accepted solution]

Hi @BradP 

You can try something like this.

Note that I've created a HolidayDateTable within this query and extracted the Holidays as a list, HolidayList. This list is used in the AddCustom step, which performs the check if a Date from that list is present in the date range between CreateDate and ClosedDate.

let
    HolidayDateTable = Table.FromColumns( {{#date(2024, 1, 1)}}, type table [Date]),
    HolidayList = List.Buffer(HolidayDateTable[Date]),
    mySample = Table.FromColumns(
        {
            {#date(2023, 12, 1), #date(2023, 12, 1), #date(2023, 12, 1)},
            {#date(2023, 12, 12), #date(2024, 1, 2), #date(2023, 12, 31)}
        }, type table [CreateDate=date, ClosedDate=date]
    ),
    AddCustom = Table.AddColumn(mySample, "ContainsHoliday", each not List.IsEmpty( 
        List.Intersect( {List.Dates([CreateDate], Number.From([ClosedDate]-[CreateDate])+1, Duration.From(1)), HolidayList} ))
    )
in
    AddCustom

  With this result

I hope this is helpful