Il y a quelques jours, Philippe a posé une question sur notre forum, concernant la répartition de montants entre différentes dates, dans Power Query. Cet article vise à vous présenter une première façon d’obtenir le résultat recherché par Philippe. Notez qu’un deuxième article suivra pour présenter une deuxième solution, encore plus raffinée.
Question de Philippe
Vous pouvez retrouver la question de Philippe sur notre forum. En voici une copie ci-dessous.
Données de départ
Nous allons démontrer deux solutions pour résoudre cette problématique. Nous sommes conscients que la première solution proposée dans le présent article est plus complexe que la deuxième solution qui sera présentée, mais elle semble plus intuitive pour les usagers d’Excel. Ce faisant, nous débuterons par présenter cette première solution, en énumérant ses avantages et ses limites et nous présenterons ensuite une deuxième solution, qui viendra contrecarrer, en partie, ces limites.
Les données fournies par Philippe ressemblaient beaucoup à celles fournies ci-dessous. Seules les dates ont été changées pour restreindre l’intervalle de temps à 12 mois.
Table Budget
Dans notre deuxième solution, nous utiliserons également une table de dates, représentant les mois retenus pour notre analyse. Toutefois, dans cette première solution, nous n’utiliserons pas cette table.
Table Dates
À noter que chacune de ces tables ont été mises sous forme de tableaux (format as table), dans le fichier Excel.
Requêtes Power Query
Au total, pour réaliser nos deux solutions, nous avons utilisé 6 requêtes, tel qu’illustré ci-dessous.
Budget intermédiaire #1
Notre première requête consiste simplement à importer la table de budget et à effectuer les manipulations suivantes:
- Supprimer les colonnes J2, J3 et Budget J2-J3
- Renommer les colonnes restantes
- Changer les formats de données
Budget intermédiaire #2
Notre deuxième requête consiste simplement à importer la table de budget et à effectuer les manipulations suivantes:
- Supprimer les colonnes J0, J1 et Budget J0-J1
- Renommer les colonnes restantes
- Changer les formats de données
Budget intermédiaire total
Notre troisième requête met simplement bout à bout les deux tables précédentes, pour ne créer qu’une seule table de budget consolidée (utilisation de la fonctionnalité Append/Ajouter).
Budget – Solution 1
Dans un premier temps, nous avons simplement ajouté une colonne faisant la différence entre les colonnes Date_fin et Date_départ afin d’obtenir le nombre de jours total entre les deux dates.
Ensuite, nous avons ajouté 12 colonnes personnalisées (vous pouvez apercevoir 4 d’entre elles ci-dessous), chacune représentant le nombre de jours effectifs dans le mois mentionné en colonne.
Chacune des colonnes calculées comprend une fonction if en 4 étapes.
Un premier if est utilisé pour considérer le nombre de jours écoulés dans le mois, lorsque le mois de la date de départ est le même que le mois de la date de la colonne. On doit alors prendre le nombre de jours entre la date de départ et la date de fin de mois de la date de départ.
Un deuxième if est utilisé pour considérer le nombre de jours écoulés dans le mois, lorsque le mois de la date de fin est le même que le mois de la date de la colonne. On doit alors prendre le nombre de jours entre la date de fin du mois précédent et la date de fin fournie.
Un troisième if est utilisé pour considérer le nombre de jours écoulés dans les mois entre la date de départ et la date de fin. On doit alors prendre le nombre de jours total dans le mois ou la différence entre le dernier jour du mois de la colonne et le dernier jour du mois précédent.
Si aucune condition n’est rencontrée, on affiche alors la valeur null. Vous trouverez un extrait du résultat ci-dessous.
Par la suite, nous avons « dé-pivoté » les colonnes (unpivot columns) pour obtenir le résultat suivant.
Nous avons ensuite appliqué le format de dates à notre colonne « Attribute » et nous l’avons transformée en date de fin de mois.
Nous avons finalement ajouté une colonne calculée afin d’obtenir le budget du mois en question, en fonction du nombre de jours couverts dans le mois.
Nous avons ensuite supprimé les colonnes qui n’étaient plus nécessaires à notre analyse.
À noter que les deux dernière requêtes ne sont utilisées que dans notre deuxième solution, qui sera présentée dans un autre article.
Résultat recherché
À l’aide de notre table résultante, nous avons pu construire le tableau croisé dynamique suivant:
Avantages et limites
Cette solution a l’avantage d’être intuitive pour les usagers d’Excel, qui travaillent fréquemment avec des colonnes calculées et qui réfléchissent en ces termes.
Cette solution a le désavantage d’exiger d’ajouter de nombreuses colonnes calculées…. une par mois couvert dans l’analyse, ce qui peut être laborieux à mettre en place. De plus, comme ces dates sont inscrites dans les titres de colonnes, elles ne sont pas dynamiques… La solution n’est donc pas vraiment réutilisable, si nos données sources évoluent et utilisent de nouveaux mois. De plus, il faut maîtriser le langage M pour rédiger tous les calculs. Il s’agit toutefois d’un mal nécessaire puisque notre deuxième solution utilisera également le langage M. Par contre, elle demandera d’ajouter une seule colonne calculée (plutôt qu’une par mois couvert par l’analyse, comme c’est le cas avec la solution actuelle) et les mois pourront être mis à jour par l’usager, à partir d’une table Excel.
Ne manquer pas la suite de cet article!
Abonnez-vous dès maintenant à notre blogue.
NOTRE OFFRE DE FORMATIONS
Le CFO masqué vous offre un vaste choix de formations Excel, notamment en tableaux de bord et en modélisation financière, ainsi qu’une série de formations en BI libre-service. 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 [email protected]
Jeu de briques
Snake
Pacman
Bubble