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

Question

Hi,&nbsp;I have 2 Power Queries in Excel, one is MainQuery and the other HolidayTable with a column HolidayDate.&nbsp; MainQuery has&nbsp; CreateDate and ClosedDate columns.&nbsp; 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) =&gt;letstartOfWeek = 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) &lt; 1 ),CountDays = if List.Count(DeleteWeekends) * 9 &gt;= 9 or HolidayCheck &gt;= 9 then 9 else 0inCountDays ,

m_dekorte · Accepted Answer

Hi&nbsp;BradP&nbsp;
&nbsp;
You can try something like this.
Note that I've created a&nbsp;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&nbsp;CreateDate&nbsp;and&nbsp;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
&nbsp;
&nbsp; With this result

&nbsp;
I hope this is helpful

Forum Discussion

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

2 Replies