Blog Post

Power BI Community Blog
2 MIN READ

Date.Networkdays function for Power Query and Power BI

ImkeF's avatar
ImkeF
Icon for Community Champion rankCommunity Champion
6 years ago
Today I'm going to share my custom NETWORKDAYS function for Power Query with you that uses the same syntax than its Excel-equivalent.   NETWORKDAYS function   This function has 3 parameters: ...
Updated 6 years ago
Version 1.0
tips & tricks
ImkeF's avatar
Icon for Community Champion rankCommunity Champion
Joined September 06, 2015
View Profile
Power BI Community Blog
On this blog you will find articles, guides and information created by our community members and, from time to time, roundups and community news. If you are interested in becoming a community blog author, send a private message to Natalie_iTalent. An overview of the community blogs can be found here.
jmschyns's avatar
jmschyns
New Member
2 years ago

Hi,

Thanks for the tuto.

It's very usefull but i have a problem with the refresh. the function take very long time and the refresh failed with time out.

Here is my code:

 

(Pays as text, optional StartDate as date, optional EndDate as date) =>

let
  HolidayList = if Pays ="Grand-Duché de Luxembourg" then LSP_CalendrierVacancesLux else LSP_CalendrierVacancesBEL,
  DateList = List.Dates(StartDate, Number.From(EndDate-StartDate)+1,#duration(1,0,0,0)),
  RemoveWeekEnds = List.Select(DateList, each Date.DayOfWeek(_,Day.Monday)< 5),
  RemoveHolidays = List.RemoveItems(RemoveWeekEnds, HolidayList),
  CountDays = if(StartDate = null or EndDate= null or Pays=null) then null else if EndDate < StartDate then 0  else List.Count(RemoveHolidays)-1
in
  CountDays
 
For my table 
let
  Source = wPeechDossiers,
  #"Lignes filtrées" = Table.SelectRows(Source, each ([EtatDossier] <> "Annulé")),
  #"Colonne conditionnelle insérée" = Table.AddColumn(#"Lignes filtrées", "DGOenPromo", each if [VenteDateF] >= [GODateD] then true else false),
  #"Lignes filtrées 1" = Table.SelectRows(#"Colonne conditionnelle insérée", each ([RDCDateF] <> null) and ([COMMBPEBPGODateF] <> null)),
  Personnalisé = Table.AddColumn(#"Lignes filtrées 1", "Workdays RDC/METRE", each WorkDays([ChantierPays], [RDCDateF], [COMMBPEBPGODateF])),
  #"Personnalisé 2" = Table.AddColumn(Personnalisé, "Workdays VNT/IPU", each WorkDays([ChantierPays], [VenteDateF],[IPUDateF])),
  #"Personnalisé 1" = Table.AddColumn(#"Personnalisé 2", "Workdays DG/DGO", each WorkDays([ChantierPays], List.Max({[DGDVDateF],[OPUDateF]}),[GODateD])),
  #"Personnalisé 3" = Table.AddColumn(#"Personnalisé 1", "Workdays IPU/OPU", each WorkDays([ChantierPays], [IPUDateF],[OPUDateF])),
  #"Personnalisé 4" = Table.AddColumn(#"Personnalisé 3","Workdays IPU/CEPI", each WorkDays([ChantierPays], [IPUDateF],[CépiDateF])),
  #"Personnalisé 5" = Table.AddColumn(#"Personnalisé 4","Workdays VNT/DGO", each WorkDays([ChantierPays], [VenteDateF],[GODateD])),
  #"Personnalisé 6" = Table.AddColumn(#"Personnalisé 5","Workdays DGO/RP", each WorkDays([ChantierPays], [GODateD],[RPDateF])),
  #"Personnalisé 7" = Table.AddColumn(#"Personnalisé 6","Workdays FGO/RP", each WorkDays([ChantierPays],[GODateF],[RPDateF])),
  #"Erreurs remplacées" = Table.ReplaceErrorValues(#"Personnalisé 7", {{"Workdays DGO/RP", ""}, {"Workdays RDC/METRE", ""}, {"Workdays DG/DGO", ""}, {"Workdays VNT/IPU", ""}, {"Workdays IPU/OPU", ""}, {"Workdays IPU/CEPI", ""}, {"Workdays VNT/DGO", ""},{"DGOenPromo", ""},{"Workdays FGO/RP", ""}}),
  #"Type de colonne changé 3" = Table.TransformColumnTypes(#"Erreurs remplacées", {{"Workdays RDC/METRE", Int64.Type}, {"Workdays DGO/RP", Int64.Type}, {"Workdays DG/DGO", Int64.Type}, {"Workdays VNT/IPU", Int64.Type}, {"Workdays IPU/OPU", Int64.Type}, {"Workdays IPU/CEPI", Int64.Type}, {"Workdays VNT/DGO", Int64.Type}, {"Workdays FGO/RP", Int64.Type}, {"DGOenPromo", type logical}})
in
  #"Type de colonne changé 3"
 
thank you for the help