Contact: 514-605-7112 / info@lecfomasque.com

Power Query – Fonctions de calendrier réutilisables

Publié le : 11 juillet 2019

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.

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

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.

 


NOTRE OFFRE DE FORMATIONS


Microsoft Most Valuable Professional

Le CFO masqué vous offre un vaste choix de formations Excel et Power BI, réparties dans 8 catégories: Excel – Tableaux de bord, Excel – Modélisation financière, Excel – VBA, Excel – Power Tools, Excel – Ninja, Power BI, Finance corporative et Compléments avancés. Ces formations sont offertes en classe, en entreprise et en ligne. Nos formateurs sont des experts dans leur domaine et ils 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.

 

Découvrez quelles formations vous conviennent

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

 

Cathy Monier

Cathy Monier, Conseil experte et Formatrice sur Ms Office et Power BI, MVP Excel de 2010 à 2018, co-auteure du livre « Power Query et le langage M » et d’une série d’autres livres sur Excel (les tableaux croisés dynamiques, Excel 2013 et VBA, Excel 2013). Elle développe avec Office des applications, bases de données et tableaux de bord pour de nombreuses entreprises, de la TPE aux grands comptes.

Laisser un commentaire