query M: Use today if endate is empty

[mxfx]

I have a function to calculate elapsed business days between two dates:

It works but if there is no EndDate, it returns an empty cell, I would like it to use "today" if there is no EndDate.

I tried creating a new parameter "Today" which would use the new column "Today" if no EndDate, I also tried with "DateTime. LocalNow" instead of a new parameter but both fail:

(StartDate as date, EndDate as date, Today as date, HolidayList as list) as number =>
let
DateList = List.Dates(StartDate, Number.From((if EndDate is null then Today else EndDate) - StartDate) , #duration(1, 0, 0, 0)),
RemoveWeekends = List.Select(DateList, each Date.DayOfWeek(_, Day.Monday) < 5),
RemoveHolidays = List.RemoveItems(RemoveWeekends, List_Holidays),
CountDays = List.Count(RemoveHolidays)
in
CountDays

or

(StartDate as date, EndDate as date, Today as date, HolidayList as list) as number =>
let
DateList = List.Dates(StartDate, Number.From((if EndDate is null then DateTime.LocalNow else EndDate) - StartDate) , #duration(1, 0, 0, 0)),
RemoveWeekends = List.Select(DateList, each Date.DayOfWeek(_, Day.Monday) < 5),
RemoveHolidays = List.RemoveItems(RemoveWeekends, List_Holidays),
CountDays = List.Count(RemoveHolidays)
in
CountDays

[mxfx]

... don't know how I did not think of that thank you!!

[JoeBarry · Accepted solution]

Hi @mxfx 

In Power Query, highlight the End Date column and in an empty cell right click and relace value. Enter a fake date.

In the formula ribbon replace the fake date

with 

Date.From(DateTime.LocalNow())

Thanks

Joe

If this post helps, then please Accept it as the solution