Dans mon article précédent, Responsables Supply Chain : VBA ou Power Query ?, je me faisais fort de vous convaincre d’utiliser Power Query, de lui donner une chance.

L’objectif premier de Power Query est de vous faire gagner des heures. et de vous les faire gagner chaque semaine, chaque mois en automatisant les tâches répétitives de transformation de vos données que vous faites dans Excel.

Alors assez de théorie et place à la pratique ! En 10 minutes chrono, nous allons construire un tableau de bord de A et Z. Et dans un mois, un simple clic sur le bouton Actualiser et vos données seront mis à jour.

Nous allons prendre un cas d’usage simple : le suivi du Chiffre d’Affaires annuel par famille de produit et sa comparaison avec le prévisionnel.

Pour obtenir ce tableau de bord dans Excel, nous allons transformer les données sources, étape par étape, grâce à Power Query. Les données cibles (après transformation) nous permettront de réaliser facilement un Tableau Croisé Dynamique ou un graphique.

Cet exercice va nous permettre de découvrir 5 fonctionnalités très utiles de Power Query que vous serez amenées à utiliser fréquemment.

Filtrer des lignes et supprimer des colonnes
Dépivoter des colonnes
Créer une nouvelle colonne
Regrouper des lignes
Fusionner les lignes de plusieurs tables

Quelles sont les données sources dont nous disposons ?

Un fichier avec le prévisionnel de CA par famille de produit : budget.xlsx
Un fichier avec les lignes de commandes livrées : commandes.xlsx

En sortie de Power Query, nous voulons récupérer une table contenant pour chaque mois et chaque famille le prévisionnel attendu et le chiffre d’affaires réalisé.

En analysant les données sources, nous pouvons identifier plusieurs transformations à effectuer :

Dans la table Budget, supprimer la colonne et la ligne de total; supprimer les lignes et les colonnes vides puis transposer le tableau du prévisionnel pour disposer d’une ligne pour chaque mois et chaque famille

Dans la table Lignes de commandes, les lignes de commande par jour et par article doivent être regroupées par mois et par famille pour correspondre à la granularité du prévisionnel

Enfin, les deux tableaux doivent être fusionnés pour disposer d’une seule table contenant à la fois le prévisionnel et le CA réalisé

Charger les fichiers sources dans Power Query

Nous avons maintenant une première analyse des opérations à effectuer. Avant d'effectuer les transformations identifiées, la première chose à faire est de charger les deux fichiers sources dans Power Query.

Créez un nouveau classeur Excel, nommez le Suivi CA 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 > Fichier > Classeur Excel), sélectionnez le classeur Budget et cliquez sur Importer.
Dans la fenêtre, sélectionnez la table Budget et cliquez sur OK.

Renouveler les mêmes opérations pour charger le classeur Commandes

Nous pouvons maintenant commencer à construire notre processus de transformation pour automatiser la mise à jour de notre tableau de bord de suivi du Chiffre d’Affaires.

Fonction n° 1 : Filtrer des lignes et supprimer des colonnes

Objectif : dans la table Budget, supprimer la colonne et la ligne de total; supprimer les lignes et les colonnes vides.

1/ Supprimer la colonne Total

Dans le volet gauche, sélectionnez la requête Budget.
Sélectionner la colonne Colonne14 qui correspond au Total par ligne
Dans le menu Accueil > Gérer les colonnes, clic sur Supprimer les colonnes > Supprimer les colonnes

2/ Supprimer la ligne Total et les lignes vides

Dans le menu déroulant de l’entête de la 1ère colonne, décocher Total
Dans le menu déroulant de l’entête de la 2ème colonne, décocher (null)

“Pour supprimer des lignes,
les filtres de colonnes, tu apprendras à utiliser”

3/ Récupérer les entêtes de colonnes

Dans le menu Accueil > Transformer, cliquez sur Utiliser la première ligne pour les en-têtes
Renommer l’entête de la Colonne1 en Famille

Fonction n° 2 : Dépivoter des colonnes

Objectif : dans la table Budget, transposer le tableau du prévisionnel pour disposer d’une ligne pour chaque mois et chaque famille

1/ Transposer le tableau

Sélectionner la colonne Famille
Dans le menu Transformer > N’importe quelle colonne, cliquez sur > Dépivoter les autres colonnes.

2/ Renommer les colonnes

Double-clic sur l’entête de la colonne Attribut et la renommer en Mois
Double-clic sur l’entête de la colonne Valeur et la renommer en CA Prévisionnel

3/ Vérifier le type de données de chaque colonne

Power Query affecte un type de données par défaut à chaque colonne. Une bonne habitude à prendre est de vérifier pour chacune des colonnes si le type est le bon.

Ici, Power Query a affecté le type Texte à la colonne Mois. Nous allons changer le type et choisir Date.

Clic Droit sur l’entête de colonne Mois. Dans le menu contextuel, choisir Modifier le type > Date.

“les types de données des colonnes,
toujours, tu vérifieras”

Fonction n° 3 : Créer une nouvelle colonne

Objectif : dans la table Commandes, ajouter une colonne Mois à la table des lignes de commandes pour pouvoir ensuite regrouper les lignes par mois

1/ Ajouter une colonne Mois contenant le 1er jour du mois correspondant à la date

Sélectionner la colonne Date. Dans l’onglet Ajouter une colonne > Date et heure de début, cliquez sur Date et sélectionnez Mois > Début du mois.
Renommer la colonne Début de mois en Mois.
Dans l’onglet Transformer > N’importe quelle colonne, sélectionnez Déplacer > Au début.

D’autres fonctions vous permettent de créer rapidement de nouvelles colonnes. Elles couvrent les besoins les plus courants comme les colonnes conditionnelles (Si … alors …), les colonnes personnalisées (pour effectuer des calculs), ou les colonnes d’index.

Fonction n° 4 : Regrouper des lignes

Objectif : dans la table Commandes, regrouper les lignes de commandes par mois et par famille.

“La granularité des tables à fusionner,
tu analyseras et si besoin, tu ajusteras”

Les lignes du budget et les lignes de commandes ne sont pas à la même granularité. Chaque ligne de commande concerne un article pour un jour donné alors que chaque ligne du budget correspond à une famille et à un mois donné.

Pour pouvoir comparer les deux montants, nous devons ramener les tables à la même granularité. Pour cela, nous allons regrouper les lignes de commandes par mois et par famille afin de pouvoir les comparer au budget.

1/ Regrouper les lignes de commandes par Mois et par Famille

Sélectionner la table Commandes
Sélectionner les colonnes Mois et Famille en maintenant la touche Ctrl.
Dans l’onglet Transformer, cliquez sur Regrouper par.

2/ Dans la fenêtre, les deux colonnes de regroupement Mois et Famille sont sélectionnées.

Saisissez CA réalisé dans Nouveau nom de colonne;
Sélectionnez Somme dans la liste Opération et sélectionnez Montant dans la liste des colonnes.
Cliquer sur OK

La table Commandes est maintenant à la même granularité (Mois et Famille) que la table Budget

Fonction n° 5 : Fusionner des tables

Objectif : Créer une table comportant à la fois le CA Prévisionnel et le CA réalisé pour chaque mois et chaque famille

Dans Excel, vous utilisez la fonction RECHERCHEV lorsque vous devez rechercher des éléments dans une table ou une plage par ligne. La formule est ensuite répétée sur toutes les cellules.

Dans Power Query, lorsqu’on souhaite fusionner les données de plusieurs tables, on sélectionne la ou les colonnes qui serviront à fusionner les deux tables. Puis, on choisit les champs de la deuxième table que l’on veut ajouter à la première table.

“RECHERCHEV, tu oublieras,
les jointures entre les tables, tu maitriseras”

Dans notre exemple, nous souhaitons fusionner la table Commandes avec la table Budget en utilisant les champs Mois et Famille. Puis, on choisit d’ajouter le champ CA prévisionnel à la nouvelle table créée.

1/ Fusionner les tables Budget et Commandes pour créer la table Suivi CA

Sélectionner la table Commandes
Dans l’onglet Accueil > Combiner, sélectionner Fusionner des requêtes > Fusionner les requêtes comme nouvelles.

Dans la fenêtre qui s’ouvre, la table Commandes apparaît en haut. Dans la partie du bas, sélectionnez la table Budget.
Dans la table Commandes, sélectionnez la colonne Mois puis dans la table Budget, la colonne Mois.
En maintenant la touche Ctrl enfoncé, sélectionnez la colonne Famille dans la table Commandes et la colonne Famille dans la table Budget. (Un petit 1 s’affiche sur les colonnes Mois et un petit 2 sur les colonnes Famille)
On conserve le type de jointure par défaut et on clique sur Ok.

Une nouvelle table est créée qui comporte les mêmes colonnes que la table Commandes plus une nouvelle colonne contenant pour chaque ligne, une table avec tous les champs de la table commandes correspondant au mois et à la famille.

Dans le volet de gauche, renommer la requête Fusionner1 en Suivi CA

2/ Ajouter le champ CA prévisionnel à la table Suivi CA.

Ouvrir le menu contextuel à droite de l’entête de la colonne Budget,
Décocher (Sélectionner toutes les colonnes) puis cocher CA prévisionnel.
Décocher Utiliser le nom de la colonne d’origine comme préfixe et cliquer sur OK.

Nous avons maintenant pour chaque mois et chaque famille, le CA réalisé et le CA prévisionnel. Il ne nous reste plus qu’à charger la table obtenue pour afficher dans Excel les données transformées sous la forme de notre choix : TCD, graphique ou autres.

Automatiser la mise à jour de vos fichiers Excel sans VBA

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 le fenêtre Importation de données, sélectionner Rapport de tableau croisé dynamique et Nouvelle feuille de calcul
Désélectionner Ajouter ces données au modèle de données et enfin cliquer sur OK

Power Query exécute le script des transformations et crée un nouveau TCD comportant les champs de la table Suivi CA. Il ne reste plus qu’à construire le TCD souhaité

Pour la mise à jour, vous n’avez qu’à écraser les fichiers sources avec les nouveaux fichiers.

Les données de votre TCD se mettent à jour automatiquement par un simple clic sur le bouton Actualiser tout (Onglet Données > Requêtes et connexions).

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

J’espère sincèrement vous avoir convaincu de donner une chance à Power Query. Prenez une heure ou deux et essayez avec vos propres données.

Pour bien démarrer avec Power BI, je vous conseille la lecture de cet article power bi: déconstruction d’un rapport simple

Si vous souhaitez d’autres exemples de KPI réalisés avec Power Query, dites le moi en commentaires

Articles Data Supply Chain