Forum Discussion

mxfx's avatar
mxfx
Frequent Visitor
2 years ago
Solved

query M: Use today if endate is empty

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



  • 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

     

2 Replies

  • JoeBarry's avatar
    JoeBarry
    Solution Sage

    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

     

  • mxfx's avatar
    mxfx
    Frequent Visitor

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