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 dans Power Query, le tout en tenant compte des jours fériés. 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 dans Power Query : Script en langage 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).

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.

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

Vous avez des sources de données volumineuses à rassembler et analyser dans Excel ? Apprenez à automatiser tout le processus d’importation, de transformation et de modélisation  des données avec nos formations sur Power Query et Power Pivot dans Excel.

Examinons maintenant ce script du calcul de jours ouvrables dans Power Query

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.

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 dans Power Query : Sans script en langage 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.

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.

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.

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

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.

À 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.

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

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.

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).

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

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

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

Conclusion

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

Fichier d’accompagnement VIP à télécharger

Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.

Formation complémentaire

Pour une introduction au langage M, suivez la formation Excel – Introduction à Power Query et au langage M.

Voici quelques commentaires d’apprenants ayant suivi cette formation :

La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.