Power Query: Trouver le nombre de jours ouvrables

Publié le 22 février 2016
par Sophie Marchand M.Sc., CPA, CGA, MVP

Power Query: Trouver le nombre de jours ouvrables

Récemment, un client m’a demandé s’il était possible de calculer le nombre de jours ouvrables entre deux dates, en tenant compte des jours fériés, via Power Query. La réponse est oui. Cet article vise à démontrer deux façons d’arriver au résulat. La première méthode consiste à rédiger un script à l’aide du code M, alors que la deuxième n’en nécessite pas.

 

Calculer le nombre de jours ouvrables : Script en code M

Pour effectuer ma démonstration, je vais utiliser les tables Dates (qui contient la date de début et la date de fin de la période sous analyse) et Holidays (qui contient les jours fériés).

Power Query Jours ouvrables code M

 

J’ai récupéré le script ci-dessous sur un forum (à quelques détails près). Celui-ci permet de calculer le nombre de jours ouvrables entre deux dates en tenant compte des jours fériés.

Ce script est expliqué plus loin dans cet article.

Power Query Jours ouvrables code M

 

Le script ci-dessus retourne le résultat suivant:

Power Query Jours ouvrables code M

 

Examinons maintenant ce script en détail.

 

Débutons par la ligne de code ci-dessous.

Cette commande demande à Excel de vérifier s’il y a une table dans le fichier Excel, qui se nomme Holidays, sinon, il demande de créer une table, qui portera le nom Holidays.

Holidays_Import =  try Excel.CurrentWorkbook(){[Name="Holidays"]}[Content] 
otherwise Table.FromColumns({{}}, {"Holidays"}),

Cette commande crée une liste à partir de la table Holidays et transforme les dates en nombre, puis garde le résultat en mémoire.

Holidays = List.Buffer(Table.TransformColumnTypes(Holidays_Import,
{{"Holidays", type number}})[Holidays]),

 

Cette commande permet de récupérer les données de la table Dates du présent fichier.

Source = Excel.CurrentWorkbook(){[Name="Dates"]}[Content],

 

Cette commande change le type de données de la table de dates pour des dates.

ChType = Table.TransformColumnTypes(Source,{{"StartDate", type date},
 {"EndDate", type date}}),

 

Cette commande ajoute une colonne calculée à la table de dates, en utilisant la fonction FxWorkingDays, avec les paramètres StartDate/EndDate (colonnes de la table Dates) et Holidays (table Holidays)). La fonction est définie plus haut dans le script et sera expliquée ci-après.

NumberOfWorkingDays = Table.AddColumn(ChType, "Workdays",
 each fxWorkingDays([StartDate], [EndDate], Holidays))

 

Les sections suivantes définissent la fonction fxWorkingDays.

 

On voit que cette fonction utilise les mêmes paramètres que la fonction NETWORKDAYS d’Excel.

fxWorkingDays = (start as date, end as date,
 optional HDays as list) as number =>

 

S’il n’y a pas de liste Holidays, on affiche null, sinon on prend la liste Holidays (une liste de dates transformées en nombre).

LstOfHolidays = if HDays = null then {} else HDays,

 

Number.From convertit les dates de la table de dates en nombre.

LstOfDays = {Number.From(start)..Number.From(end)},

 

On fait la différence entre la liste de jours comprenant tous les jours (LstOfDays) et la liste de jours qui sont des fériés, on obtient donc une liste de jours, en nombre, sans les jours fériés.

LstDiff  = List.Difference(LstOfDays, LstOfHolidays),

 

Pour chacune des dates de la liste résultante (en nombre), on fait un modulo 7.

LstMod = List.Transform(LstDiff, each Number.Mod(_, 7)),

 

Pour revoir comment utiliser la fonction MOD, vous pouvez relire l’article Connaissez-vous la fonction MOD?

Ci-dessous, on peut voir que la fonction MOD rapporte 0 pour les samedi et 1 pour les dimanches.

Power Query Jours ouvrables code M

 

On ne retient que les valeurs plus grandes que 1 (donc on retire les 0 et les 1, qui sont les samedis et dimanches).

LstSel = List.Select(LstMod, each _>1),

 

On compte le nombre d’éléments de la liste résultante.

Result = List.Count(LstSel)

 

Calculer le nombre de jours ouvrables : Sans script en code M

Si vous n’êtes pas familier avec le code M, vous pouvez néanmoins vous en sortir. Voici une procédure qui n’implique pas de connaissance majeure du code M.

 

Cet exemple utilise les 3 tables ci-dessous. La table de dates est une table de dates en continu, la table Fériés contient les jours fériés et la table Calendrier comprend la date de début et de fin de l’intervalle de temps.

Power Query Jours ouvrables code M

 

Dans cet exemple précis, nous avons un peu modifié le code M afin de pouvoir utiliser les données de notre table Calendrier mais cette étape n’est pas nécessaire. On pourrait seulement fournir une table de dates avec la date de début et la date de fin et toutes les dates intermédiaires, ce qui nous éviterait cette modification au code M. Cela dit, dans l’exemple ci-dessous, les lignes Calendrier, Date_Debut et Date_Fin sont essentielles seulement si on utilise une table Calendrier.

Et cette technique est expliquée dans l’article suivant: Power Query : Faire pointer vos requêtes vers une nouvelle source de données.

 

Power Query Jours ouvrables code M

 

D’abord, j’ai importé ma table de dates et je l’ai modifiée afin d’ajouter le jour de semaine de chacune des dates (DayOfWeek), ainsi que les samedis et les dimanches.

Power Query Jours ouvrables code M

 

Plus précisément, pour ajouter la colonne avec les jours de semaine, j’ai utilisé la fonctionnalité Date/Day/Day of Week.

Power Query Jours ouvrables code M

 

Pour déterminer les samedis et les dimanches, j’ai simplement utilisé une fonction IF (i.e. si la colonne DayOfWeek est égale à 6, afficher 1, pour les samedi et si la colonne DayOfWeek est égale à 0, afficher 1, pour les dimanches). Notez que dans Power Query, la numérotation commence toujours à 0, donc, 1 est égal à 0, 2 à 1, etc.

Power Query Jours ouvrables code M

 

Power Query Jours ouvrables code M

 

À l’étape suivante, j’ai fusionné la table de dates avec la table de jours fériés. Pour savoir comment fusionner deux tables dans Power Query, vous pouvez relire l’article : Power Query: Importer, transformer et fusionner des tables de données dans Excel.

Power Query Jours ouvrables code M 7

 

Power Query Jours ouvrables code M

 

J’ai ensuite remplacé les null par des 0, dans la colonne Férié.

Power Query Jours ouvrables code M 9

 

L’étape Lignes filtrées a été effectuée en fonction du code M généré à cause de la table Calendrier. Cette étape n’est pas nécessaire si vous n’utilisez pas de table Calendrier.

Power Query Jours ouvrables code M 10

 

J’ai ensuite ajouté une colonne pour déterminer les jours fermés (soit les jours fériés et ceux de fin de semaine).

Power Query Jours ouvrables code M 11

 

J’ai ensuite ajouté une colonne pour déterminer les jours ouvrables.

Power Query Jours ouvrables code M 13

 

J’ai ensuite supprimé des colonnes et renommé les colonnes restantes afin de ne conserver que l’information pertinente.

Power Query Jours ouvrables code M 12

 

Finalement, j’ai utilisé la fonctionnalité Group By pour faire la somme des jours ouvrables.

Power Query Jours ouvrables code M 14

 

Conclusion

Quelle méthode préférez-vous?

 

CFO-Masque_Formations-en-ligne_FB Le CFO masqué offre un vaste choix de formations en informatique décisionnelle avec Excel et Power BI, via un portail en ligne et à distance en temps réel, selon un calendrier. Si vous désirez organiser des formations privées, faites nous simplement parvenir un courriel à info@lecfomasque.com . Des certificats convenant aux normes de formation continue des divers ordres professionnels du Québec sont offerts pour l'ensemble des formations.  

Découvrez quelles formations vous conviennent

 

6 réflexions sur “Power Query: Trouver le nombre de jours ouvrables”

  1. Bonjour,

    Comment calculer le nombre d’année entre 2 dates?
    Je souhaite calculer l’âge d’une personne en fonction de la date du jour et de sa date de naissance

    Merci

  2. Autre sujet : comment calculer que deux périodes de congés sont successives en prenant en compte les samedi et dimanche ainsi que les jours fériés. exemple : deux périodes de congés du 10 juillet au 13 juillet 2017 pour la 1ière période, puis du 17 au 21 juillet 2017, sachant que le 14 juillet 2017 était férié en France (Bonjour de Paris). le collaborateur peut poser ces congés sous cette forme car il utilise deux compteurs de congés différents.
    PI : j’utilise votre méthode pour le comptage des jours travaillés en fusionnant une table des jours fériés avec un table Date et en créant une colonne ‘isWorkingDay’ qui est à 1 lorsque ‘travaillé’ et à 0 lorsque ‘non travaillé’.
    Ma question est surtout orientée autour d’une liste de période de congés avec pour chaque ligne : date de début, date de fin, type de compteur. et je dois savoir si le collaborateur a pris une période (tout compteur confondu) supérieure à 10 jours entre le 01 juin et le 31 octobre.
    Merci de votre aide.
    Denis

  3. Bonjour,
    Je comprends que vous calculez le nombre de jours ouvrés entre 2 dates d’une même colonne. Mais comment définir un délai entre 2 colonnes de dates tenant compte des jours fériés sur la période de temps définie par l’intervalle entre les 2 dates ?
    Exemple :
    Evénement – Date 1 – Date 2 – Délai hors jours fériés
    A – 1/02/2018 – 12/03/2018 – ??
    B – 8/02/2018 – 16/03/2018 – ??
    C – 16/03/2018 – 26/03/2018 – ??
    etc..

    Merci de votre aide.

  4. Bonjour,

    merci pour cet article. Le principe de transformer les dates en entier est intéressant d’un point de vue performances. Cependant, l’utilisation du modulo ne permet pas d’obtenir un résultat correct. Dans l’exemple donné, les jours dont le modulo vaut 0 et 1 sont les mercredi 3 janvier 2015 et jeudi 4 janvier 2015. Donc si on applique l’algorithme entre le 3/1/15 et le 4/1/15, on obtiendra 0 jour ouvrable ! On pourrait recaler le modulo en soustrayant 4 au numéro de jour, mais on retomberait plus tard sur le problème à cause des années bissextiles.
    Il n’y a pas d’autre choix que d’utiliser Date.Week. Je propose cette implémentation où holidays = List.Buffer(Excel.CurrentWorkbook(){[Name= »Holidays »]}[Content][Holidays]) :

    fxWorkingDays = (date1 as date, date2 as date, holidays as list) as number => let
    listdate = List.Dates(date1, Duration.Days(date2-date1), #duration(1,0,0,0)),
    listweekdays1 = List.Select(listdate, each Date.DayOfWeek(_, Day.Monday) < 5),
    listweekdays2 = List.Difference(listweekdays1, holidays),
    workdays = List.Count(listweekdays2)
    in
    workdays

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Scroll to Top