Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

NETWORKDAYS in PowerQuery Editor

Hi,

Can someone help me how to caluculate Difference between two DateTime columns without public holidays and weekend?

I have startdate and enddate in one table, Holidays dates list in other table.

It would be a great help.

Thank you. 

Need Help
  • ImkeF's avatar
    ImkeF
    6 years ago

    Hi Anonymous ,

    this function should work:

     

     

    (StartDate as date, EndDate as date, optional Holidays as list) =>
let
    ListOfDates = List.Dates(StartDate,Number.From(EndDate-StartDate),#duration(1,0,0,0)),
    DeleteHolidays = if Holidays = null then ListOfDates else List.Difference(ListOfDates, Holidays),
    DeleteWeekends = List.Select(DeleteHolidays, each Date.DayOfWeek(_,1) < 5 ),
    CountDays = List.Count(DeleteWeekends)
in
    CountDays

     

     

    reference the column with the holiday dates in its 3rd parameter

     

9 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you for a this solution. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you for your quick response.

      I would like to have it in Power Query, i want to use that column in further caluculations. Is there any function where i can enter my startdate, enddate columns and compare and exclude the weekend and holidays?

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        6 years ago

        I'm not the Power Query guru, which is why I tagged ImkeF . I will say that you could implement the DAX as a column instead of a measure.

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    2 years ago

    Hi, what if an weekend day is actually a working day by some reasons? Is there any method to calculate back that weekend day as working day? Like how to create another list for special working day which is actually in some of weekend day?