NETWORKDAYS in PowerQuery Editor

[Anonymous]

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. 

[ImkeF · Accepted solution]

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

[Syndicate_Admin]

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?

[Mariusz · Accepted solution]

Hi @Anonymous 

Please see the attached file with a solution.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

Thank you for a this solution. 

[Greg_Deckler]

@ImkeF might have a solution for Power Query. I wrote one for DAX. https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362#M109

[Anonymous]

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]

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.

[ImkeF · Accepted solution]

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

[Anonymous]

Thank you for a quik and easy solution.

[Mariusz]

Hi @Anonymous 

Thanks for accepting my solution.

Actually I think @ImkeF  solution is probably better, therefore I hope you don't mind that I marked her post as a solution as well, for the benefit of other Community members.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn