Since PowerQuery doesn't provide NetworkDays function like Excel, I decided to make one line Power Query formula to calculate NetworkDays. Actually I decided to make NetworkDays.INTL function where you can control your weekends through arguments. I immediately decide to use List.Accumulate function as this is the ideal function for this kind of scenario. (An alternative formula which doesn't use List.Accumulate is given at the end as a bonus)
This is a simple formula which doesn't simulte NetworkDays completely. Hence, it doesn't take care of the fact that ToDate <FromDate and either of the dates being blank. While these can be built as part of the formula but formula will be long and complex.
List.Accumulate is one of the functions in Power Query which facilitate the running of a loop. If you are not aware about List.Accumulate then you can probably refer to this Microsoft documentation – List.Accumulate – PowerQuery M | Microsoft Docs
In my view, this MS documentation is too small to make you understand anything. However, you can google for this and you will get some plentiful help. I think this article is an excellent one on this – https://radacad.com/list-accumulate-hidden-gem-of-power-query-list-functions-in-power-bi
The first argument of List.Accumulate demands a list. This list will need to be generated from From Date and To Date fields inclusive of both these dates. I will use this list of dates to check whether any date is a weekend date or not to exclude them from the count.
The list of dates will be generated through this
List.Dates([From],Duration.Days([To] – [From])+1,#duration(1,0,0,0))
This list will be tested against your weekend dates by this
Text.Contains("SatSun",Text.Start(Date.DayOfWeekName(c),3))
Here SatSun are weekends. You can change this parameter as per your need. Hence, if you are in a Gulf country, you will replace this with FriSat. If you have a one day weekend say Thu, replace this with Thu. If you have no weekend, replace "SatSun" with "". If you are lucky enough to be in a country which is talking to implement 3 days weekend, replace this with say "FriSatSun".
Final Power Query M-code formula would become
= List.Accumulate(List.Dates([From],Duration.Days([To] – [From])+1,#duration(1,0,0,0)),0,(s,c)=> if Text.Contains("SatSun",Text.Start(Date.DayOfWeekName(c),3)) then s else s+1)
Suppose you have a table of holidays named HolidaysList. I will be using List.Difference to remove the holidays list dates from the list of dates generated earlier. Hence, first argument of List.Accumulate will be
List.Difference(List.Dates([From],Duration.Days([To] – [From])+1,#duration(1,0,0,0)),HolidaysList[Holiday Dates])
Then you can have following M-code formula where you can control weekends as well as supply list of holidays.
= List.Accumulate(List.Difference(List.Dates([From],Duration.Days([To] – [From])+1,#duration(1,0,0,0)),HolidaysList[Holiday Dates]),0,(s,c)=> if Text.Contains("SatSun",Text.Start(Date.DayOfWeekName(c),3)) then s else s+1)
If you don't want to use List.Accumulate, you can use another formula which uses List.Transform
= List.Sum(List.Transform(List.Difference(List.Dates([From],Duration.Days([To]-[From])+1,#duration(1,0,0,0)),HolidaysList[Holiday Dates]),each (1-Number.From(Text.Contains("SatSun",Text.Start(Date.DayOfWeekName(_),3))))))
The file related to this article can be downloaded from PQA1. Hope Power Query Article 1 will prove useful to you.