Power Query – Fonctions de calendrier réutilisables

Publié le 11 juillet 2019
par Cathy Monier
Table_Temps_Faits_Plus

Power Query – Fonctions de calendrier réutilisables

Dans tout bon tableau de bord Excel ou Power BI, dès que vous devez analyser des données à partir de dates, il est important d’ajouter une table de temps. Vous pouvez bien entendu la créer avec les fonctions DAX CALENDAR et CALENDARAUTO, mais il vous reste ensuite à ajouter les infos sur l’année, le mois, …  avec des colonnes calculées.

 

Cet article a été rédigé par une collaboratrice du CFO masqué, Mme Cathy Monier.

 

Dans Power Pivot d’Excel , vous pouvez utiliser la commande Table de dates > Nouveau, de l’onglet Conception, qui crée la table avec des colonnes calculées complémentaires.

Ces nouvelles colonnes sont rapide à mettre en place mais elles ne sont pas performantes pour votre modèle de données. En effet un champ calculé est moins bien compressé qu’un champ chargé, vous alourdissez et ralentissez donc votre modèle de données. C’est pourquoi il est recommandé de créer votre table de temps à l’aide de Power Query.

Ceci n’est pas très compliqué, à partir d’une table de début et d’une date de fin, vous créez une liste de dates puis ajoutez des colonnes complémentaires pour votre analyse, tel que l’année, le numéro et le nom du mois, etc. C’est par contre pénible à refaire dans chacun de nos modèles de données.

 

Je vous propose donc un outil contenant diverses fonctions pour créer un calendrier en 1 clic dans Power Query. Cet outil est disponible dans la boutique en ligne du CFO masqué.

Pour utiliser l’une de ces fonctions :

  • ouvrez le classeur Excel
  • puis affichez les requêtes
  • copiez-la requête et collez-la dans le volet des requêtes de votre classeur

Les fonctions Calendriers faisant appel à d’autres fonctions ou paramètres, ces derniers seront également collés dans le volet de votre Power Query.

 

 

Fonctions Calendriers

Vous pouvez utiliser 3 fonctions :

  • fx_CalendrierDebutFin qui crée une table de temps sur une période
  • fx_Calendrier qui crée une table de temps dynamique à partir d’une table contenant un champ date
  • fx_CalendrierPlus est similaire à fx_Calendrier mais propose des options complémentaires

 
 

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.

 

fx_CalendrierDebutFin

Pour utiliser cette fonction, double-cliquez dessus et indiquez :

  1. la date de début
  2. la date de fin
  3. true si vous voulez le numéro de semaine selon la norme ISO8601
  4. le préfixe à ajouter au numéro de trimestre
  5. le préfixe à ajouter au numéro de semaine

Ces arguments 4 et 5 sont facultatifs, vous aurez alors les numéros de semaine et de trimestre au format nombre entier.

 

Capture_fx_CalendrierDebutFin

 

Le numéro de semaine est calculé selon 2 systèmes : le système standard de Power Query avec la fonction Date.WeekOfYear ou en utilisant la fonction fx_ISO8601Week selon la valeur de l’argument (3).
En effet, pour la norme américaine, la semaine 1 est la première semaine qu’elle soit entière ou non par contre pour la norme européenne, qui respecte la norme ISO 8601, la semaine 1 est la première semaine qui compte au minimum 4 jours. Dans Excel, vous avez maintenant la fonction NO.SEMAINE.ISO, mais vous aviez depuis longtemps le code 21 pour la fonction NO.SEMAINE afin de respecter cette norme.
Malheureusement, il n’existe pas d’argument à la fonction Date.WeekOfYear lui permettant de respecter cette norme ISO 8601. Après un peu de recherche sur Internet, je me suis donc créé ma propre fonction : fx_ISO8601Week. Vous pouvez d’ailleurs copier et coller dans votre propre classeur Excel ou dans power bi desktop pour la réutiliser.

 

Vous pouvez choisir la langue dans laquelle s’affiche le nom des mois et des jours avec le paramètre Langue. Ce dernier propose les langues Français, English, Dutch, German, Italian, Russian et Spanish.

La formule suivante permet de récupérer une table de temps :

  • entre le 01/03/2019 et le 31/03/2019
  • avec un numéro de semaine selon la norme ISO 8601
  • avec un préfixe Trim pour le trimestre et S pour le numéro de semaine
 = fx_CalendrierDebutFin(#date(2019, 3, 1), #date(2019, 3, 31), true, "Trim ", "S ")


Table_Temps_Periode


Cette table affiche les mois et jours en français car cette langue a été choisi dans le paramètre Langue.

Parametre_LangueFR

 

fx_Calendrier

Cette fonction vous permet de récupérer une table des temps dynamique à partir de votre table de faits. Celle-ci récupère :

  • le 1er jour de l’année de la 1ère date de votre table de faits
  • le dernier jour de l’année de la dernière date de votre table de faits
  • puis crée une liste de dates entre ces 2 dates.

 

Pour utiliser cette fonction, double-cliquez dessus et indiquez :

  1. la table de faits. Pour tester dans le classeur Excel ou le fichier pbix, vous avez une table qui est chargée dans une requête Exemple;
  2. le nom du champ date dans votre table de faits (important afin que la fonction travaille sur votre champ de type date). Dans l’exemple, ce champ se nomme DATES.
    Attention ! Respectez bien la casse (minuscule-majuscule) du champs de votre table;
  3. true si vous voulez le numéro de semaine selon la norme ISO8601p
  4. le préfixe à ajouter au numéro de trimestrep
  5. le préfixe à ajouter au numéro de semaine.

 

Ces arguments 4 et 5 sont facultatifs, vous aurez alors les numéros de semaine et de trimestre au format nombre entier.

 

Capture_fx_Calendrier

 

La formule suivante permet de récupérer une table de temps dynamique :

  • sur la table de faits Exemple
  • avec un numéro de semaine selon la norme ISO 8601
  • sans préfixe pour le trimestre et la semaine

= fx_Calendrier(Exemple, « DATES », true, null, null)

Le paramètre Langue est mis sur English

 

Table_Temps_Faits

 

fx_CalendrierPlus

Cette fonction vous permet de récupérer une table des temps dynamique à partir de votre table de faits comme la fonction précédente, mais elle propose plus d’options :

  • l’affichage ou non du trimestre
  • l’affichage ou non du Numéro de Semaine
  • l’ajout d’une colonne indiquant si le jour est ouvré
  • l’ajout d’une colonne indiquant si le jour est férié
  • l’ajout d’une colonne indiquant si le nombre de jours dans le mois

 

Pour utiliser cette fonction, double-cliquez dessus et indiquez :

  1. la table de faits. Pour tester dans le classeur Excel  ou le fichier pbix, vous avez une table qui est chargée dans une requête Exemple
  2. le nom du champ date dans votre table de faits (important afin que la fonction travaille sur votre champ de type date). Dans l’exemple ce champ se nomme DATES;
    Attention ! Respectez bien la casse (minuscule-majuscule) du champs de votre table.
  3. true si vous voulez afficher le trimestre
  4. true si vous voulez afficher le n° de semaine
  5. true si vous voulez savoir si le jour est ouvré
  6. true si vous voulez savoir si le jour est férié
  7. true si vous voulez afficher une colonne contenant le nombre de jours dans le mois.
  8. true si vous voulez que le numéro de semaine respecte la norme ISO8601.
  9. le préfixe à ajouter au numéro de trimestre
  10. le préfixe à ajouter au numéro de semaine
  11. une table contenant les jours fériés à prendre en compte pour le calcul des jours fériés.
  12. le nom du champ date dans votre table de jours fériés (important afin que la fonction travaille sur votre champ de type date)

 

Ces arguments 8, 9, 10, 11, 12 sont facultatifs. Les règles suivantes sont appliquées si vous ne les renseignez pas :

  • Argument SemaineIso (8) : cette option n’est prise en compte que si vous mettez true sur AfficheSemaine (4). Si vous ne renseignez pas cette option et que avez affecté la valeur true à l’option AfficheSemaine (4), le numéro de semaine sera alors calculé selon la norme américaine (équivalent à la valeur false pour cette option SemaineIso).
  • Argument PrefixeTrimestre (9) et PrefixeSemaine (10) : les numéros de semaine et de trimestre seront alors affichés au format nombre entier.
  • Argument JoursFeries (11) et NomChampJoursFeries (12) : utile si vous demandez à afficher une information sur les jours ouvrés ou les jours fériés.

 

Capture_fx_CalendrierPlus

 

La formule suivante permet de récupérer une table de temps dynamique, sur la table de faits Exemple, sans numéro de semaine ni trimestre, une info sur le jour férié et ouvré et le nombre de jours dans le mois. Les paramètres sont positionnés sur les valeurs suivantes :

  • Langue = Français
  • JoursNonOuvres = samedi, dimanche
  • Pays = France

= fx_CalendrierPlus(Exemple, « DATES », false, false, true, true, true, null, null, null, null, null)

 

Table_Temps_Faits_Plus

 

Pour calculer l’information sur les jours ouvrés et les jours fériés, il est fait appel à 2 fonctions fx_EstOuvre et fx_EstFerie.

Les fonctions sur les jours ouvrés et les jours fériés

Vous avez  à votre disposition 3 fonctions sur les jours ouvrés : fx_EstOuvre, fx_NbJoursOuvre et fx_NbJoursOuvreMois ; et 3 fonctions sur les jours fériés : fx_EstFerie, fx_JoursFeriesFrance et fx_JoursFeriesCanada.

Concernant les jours ouvrés, ces fonctions prennent en compte le paramètre JoursNonOuvres. Vous pouvez choisir l’une de ces combinaisons :

 

Jours_NonOuvres

 

Elles prennent en compte également les jours fériés afin de ne pas compter ces jours comme ouvré.

Les calculs sur les jours fériés fonctionnent en prenant en compte l’une de ces possibilités :

  • vous indiquez une table contenant vos jours fériés dans l’argument JoursFeries (facultatif) et du coup n’oubliez pas de donner le nom du champ contenant les dates fériées dans l’argument NomChamDate

 

Arguments_TableFeries

 

  • vous attribuez France au paramètre Pays
  • vous attribuez Canada au paramètre Pays et vous choisissez la province dans le paramètre ProvincesCanada

 

ProvincesCanada

 

Le principe de fonctionnement est alors le suivant :

  • si l’argument JoursFeries est attribués, les calculs se feront uniquement sur cette table de jours fériés
  • si le paramètre Pays est égal à France, la table des jours fériés est automatiquement calculées avec la fonction fx_JoursFeriesFrance.
  • si le paramètre Pays est égal à Canada, sans oublier de choisir une province dans le paramètre ProvincesCanada, la table des jours fériés est automatiquement calculées avec la fonction fx_JoursFeriesCanada.

 

Vous pouvez également utiliser ces fonctions fx_JoursFeriesFrance et fx_JoursFeriesCanada en indiquant une année afin de créer une table contenant les jours fériés de cette année

 

CreerTableFeries

 

JoursFeriesFrance

 

JoursFeriesCanada

 

J’espère que ces fonctions vous seront utiles et n’hésitez pas à me faire un retour sur les jours fériés Canadiens, ceux-ci étant un peu plus compliqués qu’en France.
 


 

Formation complémentaire

Afin d’explorer les principales fonctions et fonctionnalités de Power Query, qui permet d’importer, de transformer et de fusionner des données de diverses sources et de pouvoir les analyser efficacement, suivez la formation Excel – Introduction à Power Query et au langage M.
 

Voici quelques commentaires d’apprenants ayant suivi cette formation :

Bertrand Fortin
Écrit il y a 1 an
Simplement un gros WOW.

Je suis totalement satisfait de la formation. Je ne pouvais demandé mieux.

Stephanie Lambert
Écrit il y a 2 ans
Enfin du contenu que je ne connaissais presque pas

J'ai enfin pu avoir l'expérience d'un nouvel apprenant et comme je m'y attendais, elle fut très positive. Les notions sont bien expliquées et illustrées avec des exemples concrets. Il est très utile d'avoir les mêmes tables de données pour pouvoir reproduire les exemples. Les documents pdf fournis sont clairs et bien faits.

Daniel Harvey
Écrit il y a 2 ans
J'ai adoré.

Comme introduction a l'outil, pour avoir un aperçu de ce qu'il est possible de faire, je pense que la formation est vraiment géniale.

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

 

Laisser un commentaire

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

Scroll to Top