Récemment, un lecteur a demandé sur le forum, s’il était possible de déterminer le chiffre d’affaires cumulatif par produit, par mois, dans Power Query. Après quelques recherches, il a découvert qu’il était possible d’utiliser une fonction de table mais que cette fonction n’était pas très performante, à cause de sa nature itérative. Un autre lecteur, Stéphane Lorin, a alors suggéré une approche alternative de valeurs cumulatives dans Power Query, fort intéressante, que j’aimerais aujourd’hui, partager avec vous.
Fonction Power Query pour obtenir des valeurs cumulatives dans Power Query
Comme l’a constaté BagTote, il est possible d’utiliser la fonction ci-dessous pour créer une somme conditionnelle dans Power Query. Vous trouverez d’ailleurs cette proposition de fonction sur de nombreux sites web. Bien qu’elle retourne le résultat escompté, sa nature itérative la rend peu performante.
Fonction cumulative conditionnelle dans Power Query
L’objectif de notre exercice est d’utiliser les données du tableau en bleu (chiffre d’affaires par produit par mois sur 10 mois) et de calculer le chiffre d’affaires cumulatif par produit par mois (voir colonne K) mais par le biais de Power Query (résultat généré dans le tableau vert et expliqué dans les prochaines lignes).
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. |
|---|
Démarche proposée
1 – Importer le tableau de données mensuelles par produit dans Power Query.
2 – Fusionner la requête (table) avec elle-même sur la colonne PRODUIT.
3 – Extraire la colonne MOIS et la colonne CA de la deuxième table.
Ici, remarquez que chaque produit par mois est ainsi développé 10 fois (voyez notamment le CA de 42 qui revient 10 fois pour le produit 1, pour le mois de janvier).
5 – Effectuer un filtre sur la deuxième colonne de MOIS pour ne retenir que les MOIS inférieurs au mois en cours (remarquez ici le 42 qui n’apparaît qu’une seule fois, le 12 qui apparaît 2 fois, le 73 qui apparaît 3 fois, etc).
6 – Regrouper ensuite les données par produit, par mois et par CA, pour revenir à la table initiale, et effectuer la somme des CA.
Cette façon de faire permet de calculer aisément le total cumulatif par produit, par mois, et est plus performante car elle n’invoque pas de fonction pour chacune des lignes de la table.
Fichier d’accompagnement VIP à télécharger
Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.
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é.
