Power Query – Fonctions de calendrier réutilisables

Dans tout bon tableau de bord élaboré dans Excel (avec Power Pivot) ou dans Power BI, il est important d’ajouter une table de temps pour présenter des visuels temporels. Vous pouvez, bien entendu, créer la table de dates 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 vise plutôt à vous présenter des fonctions de calendrier dans Power Query pour créer des tables de dates adaptées à chaque contexte.

Création d’une table de dates dans Power Pivot

Dans Power Pivot, sous Excel , vous pouvez utiliser la commande “Table de dates > Nouveau”, de l’onglet “Conception”, qui crée la table dedates avec des colonnes calculées complémentaires. Ces nouvelles colonnes sont rapides à 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 vous 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.

Fonctions de calendrier dans Power Query, réutilisables !

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 de calendrier Power Query faisant appel à d’autres fonctions ou paramètres, ces derniers seront également collés dans le volet de votre Power Query.

Comment utiliser ces fonctions de calendrier dans Power Query

Voici une vidéo qui explique comment utiliser les fonctions de calendrier réutilisables dans Power Query, disponibles à notre boutique en ligne.

Fonctions de calendrier dans Power Query

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.

Complément d’information sur cette fonction de calendrier dans Power Query

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.

Langue associée au informations du calendrier

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.

Procédure pour récupérer une table de dates

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

• 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 ")





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

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.

Complément d’information sur cette fonction de calendrier dans Power Query

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.

Procédure pour récupérer une table de dates

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.

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

Complément d’information sur cette fonction de calendrier dans Power Query

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)

Précisions concernant les arguments de cette fonction de calendrier dans Power Query

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.

Procédure pour récupérer une table de dates

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)

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 :

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

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

• vous attribuez France au paramètre Pays
• vous attribuez Canada au paramètre Pays et vous choisissez la province dans le paramètre 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

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 :

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