Décider efficacement et rapidement avec Power Query et Power Pivot

Par Elauïne BERNARD

L’efficacité dans la prise de décision est l’un des objectifs primordiaux poursuivis par les entreprises. Or les décisions sont prises sous la base des informations disponibles. Alors, il est vivement conseillé de ne négliger aucune source d’informations pertinentes.

De prime abord, Il convient d’identifier les sources de données susceptibles de fournir ces informations pertinentes. Ensuite, il faut collecter ces données, les traiter, les analyser et présenter les résultats sous la forme la plus explicite possible.

Nous pouvons accomplir ces tâches avec une précision et une rapidité remarquables quand nous utilisons les outils adéquats. Parmi ces derniers, Power Query et Power Pivot occupent une place de choix. En ce sens, Moi & Technologie, partage avec vous des techniques pour utiliser Power Query et Power Pivot dans la prise de décision rapide et efficace.

Pratiquons à l’aide d’une étude de cas

Dans notre article précédent, 2 Outils Excel pour s’entrainer en Business Intelligence : Power Query et Power Pivot, nous avons présenté de manière globale des tâches fréquentes de Business Intelligence que vous pouvez réaliser avec Power Query et Power Pivot. A présent, cet article va vous montrer les marches à suivre pour exécuter ces tâches. Pour cela nous allons utiliser une étude de cas basée sur des données totalement fictives.

  • Les indications de l’étude de cas

L’entreprise JUS NATUREL, créée en 2019, produit et vend du jus naturel de 3 saveurs différents (melon, orange, citron) dans 3 départements (Nord, Ouest, Sud) du pays. Vous êtes le Data Manager de l’entreprise JUS NATUREL. Nous sommes en janvier 2021, cette entreprise vient d’avoir un nouveau directeur. Ce dernier veut se renseigner sur la situation globale de l’entreprise. Il vous demande de lui fournir les informations nécessaires en ce sens.

  • Notre solution à l’étude de cas

Pour répondre à la demande du nouveau directeur vous allez lui fournir un Tableau de Bord qui regroupe, pour les deux années d’existence de l’entreprise, les informations relatives aux :

  • Quantités vendues dans les différents départements
  • Quantités totales vendues pour les différentes saveurs
  • Evolutions des montants dépensés et entrées dans la caisse de l’entreprise

L’image ci-dessous fournit une vue du Tableau de Bord que nous allons créer ensemble pas à pas.

Tableau de Bord
  • Les données utilisées

Pour réaliser ce Tableau de Bord, vous avez contacté la direction des ventes et la comptabilité de l’entreprise JUS NATUREL. La direction des ventes vous fournira les données sur les ventes mensuelles des différents départements pour les années 2019 et 2020. La comptabilité vous fournira les données sur les dépenses et les entrées mensuelles pour les années 2019 et 2020. Si vous voulez avoir accès à ces données vous pouvez le mentionner en commentaire et nous vous les enverront.

ETL avec Power Query

Nos différentes sources de données relatives à la prise de décision étant identifiée, nous allons à présent pratiquer l’ETL des données. Nous vous rappelons que l’ETL des données consiste à Extraire, Transformer et Télécharger les données qui seront exploitées pour la prise de décision.  Dans notre étude de cas, nous allons procéder à l’ETL des données en utilisant Power Query.

  • Organisation des données

Compte tenu du fait que toutes nos sources de données soient des fichiers, la première tâche à effectuer est l’organisation les données. L’organisation des données consiste principalement à regrouper tous les fichiers de même structure dans un seul dossier. Dans notre cas, nous avons regroupé, d’une part, tous les fichiers de ventes des différents départements dans un dossier nommé Les_Ventes. D’autre part, nous avons regroupé tous les fichiers de comptabilité dans un dossier nommé Rapport_Comptabilité. Cette tâche est particulièrement importante au sens qu’elle va permettre d’extraire plus rapidement les données et d’automatiser la mise à jour des données. L’organisation des données terminée, nous pouvons passer à la tâche suivante qui consiste à accéder à l’éditeur Power Query.

  • Accéder à l’éditeur Power Query

Comme nous l’avons mentionné dans notre article, 2 Outils Excel pour s’entrainer en Business Intelligence : Power Query et Power Pivot, la manière d’accéder à Power Query dépend de la version d’Excel que vous utilisez. Si vous utilisez Excel 2010 ou Excel 2013 vous devez ajouter Power Query vous-même. Vous pouvez aller le télécharger gratuitement sur le web. Par contre, à partir d’Excel 2016, Power Query est déjà intégré dans votre classeur Excel. Il vous suffit de cliquer sur Data dans le ruban Excel et ensuite le groupe Get & Transform vous permet d’accéder aux fonctionnalités de Power Query. Dans la suite de l’article, nous allons utiliser Excel 2019. Mais rassurez-vous, vous allez pouvoir sans difficulté adapter nos différentes instructions à votre version d’Excel.

Pour accéder à l’éditeur Power Query vous pouvez suivre cette démarche : Data-> New Query->  Combine Query-> Launch Power Query Editor

Une image pour vous guider

  • Extraction des données

Nous sommes à présent dans l’éditeur de Power Query et nous allons extraire les données dossier par dossier. Procédons d’abord à l’extraction des données du dossier Les_Ventes pour les placer dans une seule feuille Excel. Ce dossier comporte 6 fichiers. Et les données de ces fichiers ont une structure identique. En effet, chaque fichier comporte une table de données de 4 colonnes (Date, Orange, Melon, Citron). Cette uniformité va nous permettre de consolider les 6 fichiers du dossier dans une seule table de données. Cette action est particulièrement importante pour la synthèse des données.

Pour extraire les 6 fichiers du dossier Les_Ventes et les placer dans une table unique vous pouvez suivre ce chemin : Editeur de Power Query-> New Source-> File-> Folder-> choix du dossier-> Combine & Transform Data-> Sheet 1 -> Skip files with errors-> ok

Quelques images pour vous guider

  • Transformation des données

Une fois les données extraites, il est fort probable que des modifications s’imposent. Un clic droit sur l’une des colonnes nous renseigne sur une bonne partie des modifications que nous pouvons y effectuer. Il s’agit entre autres de :

  • Supprimer une ou plusieurs colonnes
  • Dupliquer des colonnes
  • Ajouter une colonne de calcul
  • Renommer une colonne
  • Faire pivoter une colonne
  • Enlever des doublons
  • Enlever les données
  • Modifier le type des données
  • Remplacer une valeur par une autre
  • Faire des transformations au niveau des données textuelles et des données chiffrées

Il est également possible d’exécuter ces mêmes tâches et bien d’autres encore à partir du ruban Power Query.

Dans notre cas ici, nous n’avons pas beaucoup de modifications à effectuer. Nous allons simplement remplacer la valeur « null » par 0 pour ne pas avoir de problèmes lors des analyses.

Dans ce cas, vous pouvez suivre cette démarche : clic droit sur la colonne-> Replace value-> la valeur à remplacer et la valeur de remplacement-> ok

Quelques images pour vous guider :

Notons que l’éditeur de Power Query affiche une table de paramètres qui permet de voir les différentes actions exécutées. Et il suffit de cliquer sur la petite croix qui se trouve à côté d’une action pour l’annuler.

  • Téléchargement des données

Ces transformations terminées, nous pouvons à présent télécharger les données. Deux grandes possibilités s’offrent à nous alors : télécharger les données uniquement dans une feuille Excel ou les télécharger dans une data model. L’avantage avec une data model est que les données sont automatiquement disponibles sur Power Pivot. Dans notre cas ici, nous allons télécharger les données dans une data model.

Pour télécharger les données dans une data model, vous pouvez suivre cette démarche : éditeur de Power Query-> Close & Load-> Load To-> Add this data to the data model

Quelques images pour vous guider :

 Vous pouvez suivre le même schéma pour extraire les données du dossier Rapport_Comptabilité.

Reporting avec Power Pivot

L’étape ETL terminée, nous avons à présent accès aux données traitées.  Maintenant, nous allons créer l’outil de reporting. Il s’agit du Tableau de Bord.  Ces tâches seront réalisées pour la plupart avec Power Pivot. L’avantage principal d’utiliser Power Pivot pour faire le Tableau de Bord est la possibilité de créer des tableaux et des graphiques entre des données de tables différentes. Il suffit juste, dans ces cas, de relier les tables de données entre elles. En règle générale, Power Pivot crée ces relations de manière automatique. Et vous pouvez bien sûr les modifier suivant les logiques existantes entre vos tables de données.

  • Accéder aux données avec Power Pivot après ETL

Dans notre article intitulé 2 Outils Excel pour s’entrainer en Business Intelligence : Power Query et Power Pivot, nous l’avons clairement mentionné : peu importe la version d’Excel (à partir de 2010) que vous utilisez, Power Pivot y est automatiquement intégré. Il suffit de le chercher dans le ruban de votre classeur Excel.

Pour accéder, à partir de Power Pivot, aux données téléchargées dans la data model lors de l’ETL vous pouvez suivre cette démarche : Power Pivot-> Manage Data Model

Ensuite, vous verrez afficher les différentes feuilles qui contiennent les tables téléchargées dans la data model.

  • Réaliser un Tableau de Bord

Le Tableau de Bord est un puissant outil d’aide à la décision. Un Tableau de Bord doit être à la fois pertinent et explicite. Autrement dit, il doit répondre aux objectifs visés et permettre à la personne qui visualise le tableau de le comprendre facilement toute seule. Les principaux éléments d’un Tableau de Bord sont : Tableau, Graphique, segment et chronologie.

  • Les Tableaux Croisés Dynamiques (TCD)

Les TCD constituent les piliers du Tableau de Bord. Un Tableau Croisé Dynamique est un tableau qui permet de synthétiser les données de votre base de données et qui vous fournit un visuel suivant les critères de votre choix. Vous pouvez ainsi détecter entre autres les changements, les écarts, les extremums. Les TCD apparaissent rarement dans les Tableaux de Bord. En général, on les crée dans une feuille pour ensuite réaliser le graphique qui en découle. Et c’est ce graphique qui est le plus souvent représenté dans le Tableau de Bord. Dans le cas qui nous concerne, nous allons créer trois tableaux croisés dynamiques. Ils nous fournissent des informations concernant :

  • Les ventes des saveurs et les départements sources
  • Les ventes totales pour les différentes saveurs
  • Les dépenses et les entrées mensuelles

Pour réaliser chacun de ces TCD vous pouvez suivre la démarche suivante : Power Pivot-> Manage Data Model-> Pivot Table-> New Worksheet (pour le premier TCD)/ Existing Worksheet (pour les autres TCD) -> Choix des éléments à afficher dans le tableau

Quelques images pour vous guider :

  • Les Graphiques Croisés Dynamiques

Les graphiques constituent l’élément central du Tableau de Bord. Ils représentent généralement les TCD dans le Tableau de Bord pour attirer l’attention sur l’essentiel en un seul regard. Nous allons à présent créer les graphiques qui découlent de nos TCD.

 Pour accomplir cette tâche, vous pouvez suivre cette démarche : clic droit sur le TCD-> insert- PivotTable Tools-> Vous choisissez le type de graphique désiré

Une image pour vous guider :

Et on répète le processus pour les deux autres TCD.

Dans notre cas ici, nous allons représenter nos TCD par des graphiques dans le Tableau de Bord. Pour cela, nous allons cliquer sur les 3 graphiques et faire un copier-coller dans une nouvelle feuille. Cette action nous fournit la vue ci-dessous :

  • Segment et chronologie

A présent, nous allons créer un segment (Slicer) et une chronologie (Timeline). Ces deux éléments ont des propriétés de filtre et de connexion. D’une part, ils permettent de visualiser les graphiques suivant des critères spécifiques. D’autre part, nous pouvons les utiliser pour relier les différents tableaux et graphiques du Tableau de Bord. Pour notre étude de cas, notre segment sera créé à partir de notre graphique sur Les ventes des saveurs et les départements sources etnotre chronologie à partir de notre graphique sur Les dépenses et les entrées mensuelles.

Pour créer le segment, vous pouvez suivre la démarche suivante : clic droit sur le graphique en question -> insert-> Slicer-> choix du critère filtre-> ok

Pour connecter le segment aux autres graphiques : Clic droit sur le segment-> Report Connection-> Cocher les graphes dont vous désirez connectés au segment.

Pour créer la chronologie, vous procèderez de la même manière sauf qu’au lieu de choisir Slicer vous choisissez Timeline.

Quelques images pour vous guider :

  • Mise en forme du Tableau de Bord

Les éléments fondamentaux étant créés, il ne nous reste qu’à appliquer des designs pour embellir le Tableau de Bord. Par exemple, dans notre cas ici, nous avons ajouté des formes pour encadrer le tableau de Bord et inséré un titre.

Et voilà vous venez de créer le Tableau de Bord que vous allez envoyer au nouveau directeur de l’entreprise JUS NATUREL.

Décider en temps réel : mise à jour des données

 Supposons maintenant que vous soyez au 1er juillet 2021. Le nouveau directeur vous dit qu’il veut avoir une idée de l’évolution de la situation depuis son arrivée. Il vous demande dans combien de temps vous pouvez lui fournir ce travail. Vous pouvez lui répondre 15 minutes sans hésiter.

En effet vous donnez aux responsables des ventes et de comptabilités 10 minutes pour qu’ils vous fournissent les données de 2021. Et durant les 5 dernières minutes vous aller procéder à la mise à jour des données.

Pour effectuer la mise à jour vous pouvez suivre cette démarche : Transférer les nouveaux fichiers dans les dossiers de même structure qui existaient déjà–> ouvrir le fichier contenant le Tableau de Bord que vous avez créé avant-> activer le fichier en cliquant sur le bouton activer-> Data-> Refresh All

Conclusion : Comment décider efficacement et rapidement avec Power Query et Power Pivot ?

Nous avons exploré à travers cet article les étapes de l’élaboration, avec Power Query et Power Pivot, d’un outil efficace pour la prise de décision rapide : un Tableau de Bord. Il s’ensuit que les principales marches à suivre pour réaliser un Tableau de Bord avec Power Query et Power Pivot dans un temps record sont respectivement l’organisation des données, l’ETL des données, la réalisation des Tableaux et Graphiques Croisés Dynamiques et la création de segment et de chronologie.

Cet article s’avère donc un guide vous permettant d’optimiser votre temps en matière de manipulation de données utiles à la prise de décision. Les informations qui s’y trouvent vous permettront bel et bien de décider efficacement en utilisant les outils Power Query et Power Pivot du logiciel Microsoft Excel.

Cet article vous a été utile ? L’avez-vous aimé ? Moi & Technologie vous invite à laisser vos impressions en commentaires.

Nous vous invitons également à rester connecter à Moi & Technologie pour apprendre davantage sur Power Query et Power Pivot.

Par Elau

Je trouve que chaque jour est une opportunité pour créer et apprendre. Seul, nous pouvons avoir de grande vision. En équipe nous pouvons réaliser les grandes visions. Apprenons, Créons et Réalisons ensemble pour progresser jour après jour.

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l’aide de votre compte WordPress.com. Déconnexion /  Changer )

Photo Google

Vous commentez à l’aide de votre compte Google. Déconnexion /  Changer )

Image Twitter

Vous commentez à l’aide de votre compte Twitter. Déconnexion /  Changer )

Photo Facebook

Vous commentez à l’aide de votre compte Facebook. Déconnexion /  Changer )

Connexion à %s