Dans cet article, nous allons apprendre la méthode pour générer un échéancier mensuel à partir d'un tableau de contrats avec dates de début et de fin. La méthode utilisée peut être appliquée à d'autres données autres que celles de contrats et à une fréquence autre que mensuelle. (La méthodologie complète est à retrouver aussi en vidéo à la fin de l'article.)

‍

Grâce à Power Query et aux fonctions List.Dates et List.Select, vous pourrez créer ce type d’échéancier automatiquement, sans formule complexe, et avec une méthode réutilisable efficacement y compris lorsque les volumes de données augmentent.

‍

Dans Excel, il est courant d’avoir une liste de contrats avec une date de début et une date de fin. Mais pour analyser l'activité mensuelle, vous avez besoin d'un tableau où chaque ligne correspond à un mois d’exécution. Générer ces lignes manuellement est long, source d’erreurs, et impossible à maintenir sur 10 000 contrats.

‍

Avec Power Query, vous pouvez transformer automatiquement chaque ligne d'un contrat en une série de dates mensuelles, avec des étiquettes indiquant le début, la fin ou la période du contrat. Cette approche est dynamique, performante, et totalement sans formule Excel : tout se passe dans l’éditeur Power Query.

‍

‍

‍

‍

Vous partez d'un tableau Excel nommé Contrats, avec les colonnes suivantes :

• Contrat : identifiant du contrat
• Début : date de début
• Fin : date de fin

‍

L’objectif est de générer un échéancier mensuel de X dates pour chaque contrat, avec :

• Une ligne par mois couvert par le contrat
• Une indication : "Début", "Fin" ou "-"
• Une structure prête à l’analyse ou l’export

‍

Voici la méthodologie étape par étape :

‍

#1 | Charger les contrats dans Power Query

‍

L'objectif de cette première étape est de charger les données de la feuille vers une table dans l'éditeur Power Query. 

‍

1. Allez dans l’onglet Données > Obtenir et transformer les données
2. Choisissez Depuis un tableau ou une plage (le tableau doit être un tableau structuré)
3. L'éditeur de Power Query s’ouvre avec la table Contrats table
4. Modifier le type de données des colonnes de dates pour n'afficher que les dates (sans les heures)

‍

Les données sont prêtes pour pouvoir effectuer les transformations suivantes dans l'éditeur de Power Query. 

‍

#2 | Générer les dates des contrats

‍

L’objectif est maintenant de générer des listes de toutes les dates entre Début et Fin, avec un intervalle d'un mois, et ce pour chaque contrat, c'est-à-dire pour chaque ligne. 

‍

1. Cliquez sur Ajouter une colonne > Colonne personnalisée
2. Indiquez le nom de la colonne à ajouter, par exemple Echéancier
3. Saisissez la formule suivante (langage M) :

‍

^{let
debut = [Début],
fin = [Fin],
 listeJours = List.Dates(
  debut,
 Duration.Days(fin - debut) + 1,
  #duration(1, 0, 0, 0)
 ),
 jourDuDebut = Date.Day(debut),
 listeFiltrée = List.Select(listeJours, each Date.Day(_) = jourDuDebut)
in
 listeFiltrée}

‍

Cette formule génère une liste de toutes les dates entre la date de début et celle de fin, puis filtre ensuite les dates créées pour ne conserver que celles dont le numéro du jour dans le mois correspond au numéro du jour dans le mois initial (par exemple, le 25 de chaque mois).

‍

#3 | Développer les lignes mensuelles

‍

L'objectif de cette troisième étape est de transformer le tableau initial de X lignes en un tableau final de Y lignes, en passant d'une ligne par contrat à une ligne par mois et par contrat. 

‍

1. Développez la colonne Echéancier en lignes via l’icône à double flèche avec l'option Développer sur de nouvelles lignes
2. Renommez la colonne obtenue en Date
3. Typer la colonne Date dans le type Date

‍

Vous avez maintenant autant de lignes que de mois dans chaque contrat. Il reste à identifier si chaque date correspond au début, à la fin ou à une période.

‍

‍

#4 | Identifier chaque période

‍

L'objectif est donc d'ajouter une colonne supplémentaire, indiquant soit Début, soit Fin, soit rien, pour chaque date de chaque contrat. 

‍

1. Cliquez sur Ajouter une colonne > Colonne conditionnelle
2. Nommez cette colonne Position
3. Complétez l'éditeur avec la logique suivante :
   • Si colonne Date = colonne Début alors Début
   • Si colonne Date = colonne Fin alors Fin
   • Sinon : "-"
4. Réorganisez l'ordre des colonnes si besoin
5. Supprimez les colonnes Début et Fin qui ne sont désormais plus nécessaires

‍

Vous obtenez un échéancier clair, où chaque ligne représente un mois du contrat et où certaines lignes sont tagguées comme étant le mois de début et de fin du contrat.

‍

‍

#5 | Charger l'échéancier dans un tableau

‍

Vous pouvez désormais choisir où vous voulez faire apparaître votre échéancier mensuel dans un tableau structuré, lui-même dans une feuille. (Vous pourriez aussi charger directement les données mensualisées dans un TCD ou dans le modèle de données de Power Pivot.)

1. Cliquez sur Accueil > Fermer et charger dans > Tableau
2. Positionnez votre tableau là où vous le souhaitez

‍

Votre échéancier est maintenant dynamique : toute mise à jour dans le tableau d’origine sera automatiquement répercutée dans le tableau créé, à condition de cliquer sur Actualiser tout dans l'onglet Données du ruban. 

‍

‍

🎁🎁🎁 Retrouvez la démonstration complète en vidéo ici : https://www.youtube.com/watch?v=zppmYArA0KA

‍

‍

Eléments à retenir :

‍

Fonction List.Dates : Elle permet de créer une liste de dates en fonction de 3 arguments, dont la date de début. 

_{List.Dates( start as date, count as number, step as duration ) as list}

‍

Fonction List.Select : Elle permet de filtrer une liste existante en fonction d'une ou plusieurs conditions. 

_{List.Select(list as list, selection as function) as list}

‍

Ces deux fonctions du langage M n'existent pas dans l'éditeur no-code Power Query. Elles sont cependant très utiles et même obligatoires pour pouvoir générer des échéanciers, des plannings, ou autres, dans Power Query.