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 :

Annie Tremblay
Écrit il y a 5 jours
J'ai eu la chance que mon employeur m'offre l'ensemble des parcours.

C'est vraiment une opportunité d'apprendre et d'élargir mes connaissances. Sophie Marchand est une excellente formatrice et la combinaison des vidéos et exercices aident a consolider les apprentissages. Merci pour tout!

Amélie Tremblay
Écrit il y a 4 mois
Une des rares formations où nous quittons avec une boîte à outils.

Très bonne formation applicable à ma réalité. Une des rares formations où nous quittons avec une boîte à outils.

Cécile BERNARD
Écrit il y a 5 mois
Je ne regrette absolument pas mon choix !

Excellent !! Je suivais déjà Sophie via Twitter ou directement sur le site du CFO masqué (forum et astuce) et quand il a fallu déterminer quel organisme de formation prendre, j'ai naturellement pensé au CFO Masqué. Je ne regrette absolument pas mon choix !

Stephanie Lambert
Écrit il y a 2 ans
Enfin du contenu que je ne connaissais presque pas

J'ai enfin pu avoir l'expérience d'un nouvel apprenant et comme je m'y attendais, elle fut très positive. Les notions sont bien expliquées et illustrées avec des exemples concrets. Il est très utile d'avoir les mêmes tables de données pour pouvoir reproduire les exemples. Les documents pdf fournis sont clairs et bien faits.

CFO-Masque_Formations-en-ligne_FB Le CFO masqué offre un vaste choix de formations en informatique décisionnelle avec Excel et Power BI, via un portail en ligne et à distance en temps réel, selon un calendrier. Si vous désirez organiser des formations privées, faites nous simplement parvenir un courriel à info@lecfomasque.com . Des certificats convenant aux normes de formation continue des divers ordres professionnels du Québec sont offerts pour l'ensemble des formations.  

Découvrez quelles formations vous conviennent

 

Laisser un commentaire

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

Scroll to Top