Je vous propose un nouvel exercice pratique avec Power Query : la création d’un tableau de bord pour mener une analyse ABC.

Cette fois-ci, on change un peu de braquets. Le rapport reste simple, on le fait d’ailleurs dans Excel très facilement. Mais l’exercice, une fois encore, va nous permettre de découvrir quelques fonctions très utiles de Power Query.

Qu’est-ce que la classification ABC ?

L’analyse ABC est une méthode utilisée pour concentrer les efforts et les ressources sur les éléments qui comptent le plus pour l'entreprise. Le principe de Pareto appliqué à la gestion des stocks. 20% de vos produits représentent 80% de votre CA. Il s’agit de classer les articles en 3 classes.

Classe A : 20% des produits ; 80% du CA
Classe B : 30% des produits ; 15% du CA
Classe C : 50% des produits ; 5% du CA

Les résultats attendus de la méthode, appliquée à la gestion des stocks, sont un meilleur contrôle des pièces critiques et une diminution des stocks de sécurité.

Pourquoi utiliser Power Query ?

Aujourd’hui de nombreux logiciels d'inventaire de stocks proposent une analyse ABC. Si ce n’est pas le cas ou si vous ne disposez pas de ce type de logiciel, il y a de fortes chances que vous utilisiez Excel.

Il s’agit de classer par ordre décroissant tous les articles en fonction du montant des ventes. Ensuite, les seuils sont appliqués par rapport à la part cumulée du montant des ventes. Une représentation graphique aide à déterminer les seuils qui seront utilisés.

Pourquoi utiliser Power query, alors ? Je vois au moins trois raisons d’automatiser le calcul de vos classes ABC :

Le nombre de références : lorsque le nombre d’articles est important, les transformations des données dans Excel deviennent vite fastidieuses et sources d'erreurs. Ce qui se fait facilement avec une centaine d’articles, devient risqué lorsqu’on parle de milliers d’articles.

La préparation des données : les données sources doivent souvent être préparées avant de pouvoir calculer la classification ABC. Fusionner plusieurs fichiers; filtrer les données sur la période choisie, compiler les fichiers de plusieurs sources.

La fréquence de la mise à jour : une analyse ABC se fait sur des classes à jour (nouveau produit, produit obsolète, évolution des ventes). Sans mise à jour régulière de la répartition des articles dans les classes, les mesures correctives finissent par être appliquées aux mauvais articles.

Power Query est totalement intégré dans Excel. Il automatise vos transformations de données et ceci sans (presque) écrire une ligne de code. Chaque étape est enregistrée. Un simple clic sur le bouton Actualiser et vos données sont mis à jour.

Analyse du rapport

Pour calculer les classes qui serviront à notre analyse ABC, nous devons déterminer les paramètres suivants : le nombre de classes; l’unité pour mesurer le “poids” de chaque article; l’étendue historique de la mesure et les pourcentages utilisés comme seuil pour chaque classe.

Dans notre exemple, nous avons retenu les valeurs suivantes :

Nombre de classes : 3 (A, B, C)
Unité : Le montant des ventes en euros
Période : La dernière année calendaire
Seuils : classe A entre 0 et 80%; B entre 80 et 95%; C entre 95 et 100%

Notre point de départ est une table comportant 2 colonnes : le nom de l’article et le montant en euros des ventes pour l’année choisie.

Les transformations que nous devons effectuer dans Power Query sont les suivantes :

Calculer le montant cumulé des ventes et le rapport en pourcentage de ce montant et du montant total des ventes.
Calculer le rang de chaque article et le rapport en pourcentage du rang et du nombre total d’articles.
Calculer la classe de chaque article en fonction des seuils déterminés.

Si vous devez transformer vos données sources pour obtenir notre table de départ comportant pour chaque article, le montant des ventes de l’année, je vous encourage à lire cet article : KPI Supply Chain : Suivi du CA avec Power Query. Vous y apprendrez comment facilement trier, filtrer vos données ou fusionner des tables.

Chargement des données sources

Dans notre exemple, nous allons utiliser les données de la base Northwind de Microsoft. Northwind Traders est une organisation fictive qui gère les commandes, les produits, les clients, les fournisseurs et de nombreux autres aspects d’une petite entreprise.

Toutes ces données sont accessibles depuis Excel en utilisant le protocole OData. C’est une des très nombreuses possibilités pour charger des données dans Power Query.

Créez un nouveau classeur Excel, nommez le Analyse ABC et ouvrez le.
Dans l’onglet Données > Obtenir des données > Sélectionner Lancer l’éditeur Power Query
Dans l'onglet Accueil > Nouvelle requête, sélectionnez Nouvelle source > Autres sources > Flux OData.
Dans la fenêtre Flux OData, coller dans le champ URL l’adresse suivante :

https://services.odata.org/v2/northwind/northwind.svc/

Cliquez sur OK.
Dans la fenêtre Navigateur, sélectionnez la table Product_Sales_for_1997 et cliquez sur OK.

Les données de la table sont chargées dans Power Query. Nous avons pour chacun des articles le montant des ventes de l’année.

Nous pouvons maintenant commencer à construire notre processus de transformation pour automatiser la mise à jour du calcul des classes pour mener notre Analyse ABC.Pour commencer, nous allons mettre au propre notre Table de données.

Supprimer la colonne CategoryName
Renommer la colonne ProductName en Article
Renommer la colonne ProductSales en Montant

Calcul du montant cumulé et du rang en pourcentage

Objectif : Calculer le montant cumulé des ventes et le rapport en pourcentage de ce montant et du montant total des ventes. Calculer le rapport en pourcentage du rang et du nombre d’articles

Étape n° 1 : Créer la colonne Montant cumulé

Il existe de nombreuses façons de créer cette colonne cumulative, Elles diffèrent sur leur niveau de complexité, leur utilisation du langage M et leur performance d'exécution. Pour notre exemple, nous allons reproduire les étapes que nous ferions sous Excel.

Dans le menu de l’entête de la colonne Montant, sélectionnez Tri décroissant
Dans le menu Ajouter une colonne > Général, Sélectionnez Colonne d’index > À partir de 1
Dans l’onglet Ajouter une colonne > Général, cliquer sur Colonne personnalisée

Dans la fenêtre, saisir Montant Cumulé dans le champ Nouveau nom de colonne
Dans le champ Formule de colonne personnalisé, saisissez la formule suivante :

List.Sum(List.FirstN(#"Index ajouté"[Montant],[Index]))

Vérifier qu’aucune erreur de syntaxe n’a été détectée et cliquer sur OK
Cliquer sur l'icône à gauche de l’entête de la colonne Montant cumulé et sélectionnez Nombre décimal

La formule que nous avons saisi fonctionne de la manière suivante : Elle sélectionne tous les montants des lignes précédentes (List.FirstN) en utilisant la colonne Index puis elle en fait la somme (List.Sum). #”Index ajouté” est le nom de l’étape précédente.

Étape n° 2 : Créer les colonnes Montant total et Nombre d’articles

Nous allons ajouter 2 colonnes à notre table : une colonne avec le montant total des ventes et l’autre avec le nombre d’articles. Nous en aurons besoin ensuite pour calculer les autres colonnes.

Dans l’onglet Transformer > Tableau, cliquez sur Regrouper par

Dans la fenêtre, cliquez sur Avancé
Cliquez sur les … à côté du champ de regroupement et sélectionnez Supprimer
Ajouter deux autres agrégations et paramétrez les champs comme indiqué sur l’image ci-dessus

Nous regroupons l’ensemble des lignes en une seule qui contient 3 colonnes : Une colonne avec le total des ventes, une colonne avec le nombre d’articles et une dernière qui contient toutes les lignes de notre table.

Il ne reste plus qu'à développer la table pour récupérer l’ensemble des lignes.

Ouvrir le menu contextuel à droite de l’entête de la colonne Détails,
Décocher Utiliser le nom de la colonne d’origine comme préfixe et cliquer sur OK.

Étape n°3 : Créer les colonnes % Montant cumulé en % Rang

Nous allons ajouter 2 nouvelles colonnes pour obtenir pour chaque article le montant cumulé en % et le rang en %

Dans l’onglet Ajouter une colonne > Général, cliquer sur Colonne personnalisée
Dans la fenêtre, saisir % Montant cumulé dans le champ Nouveau nom de colonne
Dans le champ Formule de colonne personnalisé, saisir la formule suivante :

[Montant cumulé]/[Montant Total]

Vérifier qu’aucune erreur de syntaxe n’a été détectée et cliquer sur OK
Cliquer sur l'icône à gauche de l’entête de la colonne % Montant cumulé et sélectionnez % Pourcentage

Nous répétons maintenant les mêmes opérations pour créer la colonne % Rang avec la formule suivante :

[Index]/[#"Nombre d'articles"]

Nous avons à présent tous les éléments nécessaires pour calculer les classes ABC. Nous pouvons supprimer les colonnes Montant total et Nombre d’articles qui ne nous serviront plus.

Calcul de la classification ABC

Objectif : Calculer la classe de chaque article en fonction des seuils déterminés.

Calculer les classifications ABC consiste à créer une colonne conditionnelle. En fonction du pourcentage de montant cumulé de l’article, nous allons lui affecter une des 3 classes.

Dans le menu Ajouter une colonne > Général, Cliquez sur Colonne conditionnelle
Nommer la nouvelle colonne : Classe
Définissez les conditions comme ci-dessous

Si % Montant cumulé est inférieur ou égale à 0,8 Alors A
Si % Montant cumulé est supérieur à 0,95 Alors C
Autre B

Cliquez sur OK

Nous y sommes ! Nous avons répartis nos articles en 3 classes en fonction du pourcentage de montant cumulé.

Nous avons maintenant tous les éléments pour constituer notre tableau de bord.

Création du rapport

Pour exploiter nos données transformées dans une feuille Excel, nous devons les charger dans Excel et choisir la restitution souhaitée.

Dans l’onglet Accueil > Fermer, sélectionner Fermer et Charger, puis sur Fermer et Charger dans.
Dans la fenêtre Importation de données, sélectionner Tableau et Nouvelle feuille de calcul et enfin OK

Power Query exécute le script des transformations et crée un nouveau Tableau.

Dans notre exemple, l’application des seuils par défaut donne la répartition suivante :

La classe A pour 80% du montant des ventes et 46% du nombre d’articles
La classe B pour 15% du montant des ventes et 29% du nombre d’articles
La classe C pour 5% du montant des ventes et 25% du nombre d’articles

Si vous vous rappelez, les seuils de nos classes ont été choisis de manière arbitraire. La représentation graphique nous permet d’affiner les seuils des classes. Par exemple, en diminuant le nombre d’articles de la classe A et en augmentant celui de la classe C

Pour modifier les seuils des classes, nous devons modifier la formule de la colonne Classe.

Ouvrez Power Query,
Sélectionner l’étape Colonne conditionnelle ajoutée
Modifier les valeurs des deux conditions.

Il ne nous reste plus qu’à refermer Power Query et à actualiser le jeu de données.

Ajuster dynamiquement les seuils des classes

Il est possible de modifier notre requête de transformation pour rendre dynamique les seuils des classes. Pour cela, nous devons remplacer les valeurs de seuil de la colonne conditionnelle Classe par des variables.

Nous avons besoin de 2 valeurs : le seuil supérieur de la classe A et le seuil inférieur de la classe C. Comme d’habitude avec Power Query, il existe plusieurs techniques. Je vous en présente deux.

Méthode n°1 : En utilisant des paramètres

Dans Power Query, nous créons les 2 paramètres :

Dans le menu Accueil > Paramètres, cliquez sur Gérer les paramètres > Nouveau paramètre.
Dans la fenêtre, saisir le Nom : Classe A, sélectionnez le Type : Nombre décimal, saisissez la valeur actuelle : 0,65

Procédez à l’identique pour le 2ème paramètre : Nom : Classe C, sélectionnez le Type : Nombre décimal, saisissez la valeur actuelle : 0,85.

Il nous faut maintenant remplacer dans la formule de la colonne conditionnelle, les valeurs par le nom des paramètres.

Dans le panneau Requêtes, Sélectionnez la requête Product_Sales_for_1997
Dans le panneau Étapes appliquées, sélectionnez l’étape Colonne conditionnelle ajoutée et cliquez sur l’icône à droite du nom de l’étape

Dans la fenêtre, pour la première condition, dans la partie Valeur, sélectionnez Paramètres dans le menu de gauche puis sélectionnez Classe A
Pour la deuxième condition, dans la partie Valeur, sélectionnez Paramètres dans le menu de gauche puis sélectionnez Classe C
Cliquez sur OK
Dans le menu Accueil, cliquez sur Fermer et charge

Les valeurs de la formule ont été remplacées par le nom du paramètre. Il suffit maintenant de changer la valeur du paramètre pour mettre à jour le calcul des classes.

Pour modifier la valeur d’un des paramètres :

Dans le menu Données > Requêtes et connexions d’Excel, cliquez sur Requêtes et connexions pour afficher le volet.
Dans le volet, clic droit sur le nom du paramètre puis clic sur Modifier
Dans la fenêtre Power Query, Modifier la valeur du paramètre
Dans le menu Accueil, cliquez sur Fermer et charger
Cliquez sur Actualiser pour recalculer les classes ABC

Méthode n°2 : En utilisant des tableaux dans Excel

L’avantage de cette deuxième méthode est de pouvoir mettre à jour les valeurs des seuils sans avoir à ouvrir Power Query.

Dans la feuille Excel, créez 2 tableaux avec chacun 1 ligne correspondant à la valeur du paramètre.
Nommez les tableaux ClasseA et ClasseC

Chargez les 2 tableaux dans Power Query : Dans le menu Données > Récupérer et transformer des données, cliquez sur À partir d’un tableau
Dans la fenêtre Power Query, sélectionnez la cellule avec la valeur puis clic droit et sélectionnez Drill down

Dans le panneau Requêtes, Sélectionnez la requête Product_Sales_for_1997
Dans le panneau Étapes appliquées, sélectionnez l’étape Colonne conditionnelle ajoutée
Dans la formule, remplacez la valeur par le nom de la requête

Dans le menu Accueil, cliquez sur Fermer et charger

Les valeurs de la formule ont été remplacées par le nom de la requête. Il suffit maintenant de changer la valeur dans le tableau Excel pour mettre à jour le calcul des classes.

Pour mettre à jour la répartition de vos articles dans les classes ABC, vous n’avez qu’à remplacer le fichier source avec le nouveau fichier. Les données se mettent à jour automatiquement par un simple clic sur le bouton Actualiser tout (Onglet Données > Requêtes et connexions).

Si vous voulez ajuster les seuils de vos classes, vous modifiez leurs valeurs. Et avec un nouveau clic sur le bouton Actualiser, Power Query prend en compte les nouvelles valeurs des seuils pour calculer la répartition.

Pour aller plus loin, les références Microsoft des fonctions étudiées :