Somme cumulative conditionnelle dans Power Query

Publié le 16 septembre 2019
par Sophie Marchand M.Sc., CPA, CGA, MVP
Développer table

Somme cumulative conditionnelle dans Power Query

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, fort intéressante, que j’aimerais aujourd’hui, partager avec vous.

 

Fonction Power Query pour créer une somme conditionnelle

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.

Problème performance

 

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

Somme.si.ens dans Power Query
 
 

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.

CA par produit

 

2 – Fusionner la requête (table) avec elle-même sur la colonne PRODUIT.

Fusionner une table avec elle-même

 

3 – Extraire la colonne MOIS et la colonne CA de la deuxième table.

Développer 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).

Tables fusionnées

 

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

Filtre mois

 

6 – Regrouper ensuite les données par produit, par mois et par CA, pour revenir à la table initiale, et effectuer la somme des CA.

Regrouper par

 

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 :

Le CFO masqué - Commentaires formation - Excel – Introduction à Power Query et au langage M
CFO-Masque_Formations-en-ligne_FBLe CFO masqué offre un vaste choix de formations en ligne en informatique décisionnelle avec Excel et Power BI. Si vous désirez organiser des formations de groupe privées, faites nous simplement parvenir un courriel à info@lecfomasque.com.

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Scroll to Top