Magazine Informatique

Power Query: Trouver le nombre de jours ouvrables

Publié le 22 février 2016 par Sopmar01 @mon_cher_watson

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 fonctoin 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?


NOTRE OFFRE DE FORMATIONS


Microsoft Most Valuable Professional

Le CFO masqué vous offre 13 formations, réparties dans 4 catégories: Tableaux de bord, Modélisation financière, Finance corporative et Analyse et modélisation de données. Ces formations sont offertes en classe, en entreprise et en ligne. Nos formateurs sont des experts dans leur domaine et sont accrédités par Emploi-Québec et vous remettent un certificat, à la fin de chaque formation, que vous pouvez notamment utiliser pour faire reconnaître des heures de formation continue auprès de votre ordre professionnel.

Consulter la liste des formations offertes

Pour info: 514-605-7112 ou info@lecfomasque.com


Retour à La Une de Logo Paperblog

A propos de l’auteur


Sopmar01 3934 partages Voir son profil
Voir son blog

l'auteur n'a pas encore renseigné son compte l'auteur n'a pas encore renseigné son compte