Nous avons vu dans le chapitre précédent comment nous pouvons utiliser Access pour stocker nos données. Nous allons voir maintenant un nouvel outil qui s’appelle Power Query. Cet outil est un ETL (pour Extract Transform Load). Il existe plusieurs logiciels d’ETL, comme Alteryx, Knime, Dataiku ou encore Power Query à plus petite échelle.
Découvrez les fonctionnalités de Power Query
Power Query est présent depuis plusieurs années chez Microsoft et il est particulièrement important.
Cet outil permet de se connecter à des sources de données et de faire une transformation des données directement dans Excel.
Ce qui est encore plus important, c’est que cet outil est maintenant présent dans l’outil de visualisation de données de Microsoft Power BI. En effet, Power Query est une des briques principales de Power BI.
Nous allons voir dans ce chapitre comment nous pouvons importer des données et commencer à les traiter. Mais il faudrait un cours complet pour pouvoir tout comprendre sur cet outil (transformation avancée, langage DAX, etc.).
En fait, c’est encore un outil qui permet de stocker des données et de les traiter ?
Dans l’ensemble, je peux vous répondre oui, mais cela va plus loin. Demandons-nous pourquoi nous avons besoin d’un outil comme celui-là ? Qu'est-ce qui a changé cette dernière décennie ?
J’aurais tendance à dire le volume des données peut-être, ce qu’on appelle le big data ?
Le big data est effectivement une tendance des dernières années avec la multiplication du volume des données. Mais nous pouvons également noter la diversité des données. Il y a quelques années, nous n’utilisions que “quelques” sources de données, aujourd’hui nous avons des données partout, et c’est justement sur ce point que Power Query devient intéressant. Cet outil permet de faire la jonction entre de gros volumes de données (sans exagérer, il ne s’agit pas de bases de données de plusieurs dizaines de Go) et, surtout, plusieurs sources de données.
Nous pouvons donc rapidement connecter une base Access avec trois fichiers Excel, une base MySQL et une base de données Azure.
Voilà la vraie valeur des logiciels d’ETL, ils permettent de faire la jonction entre toutes les sources de données, tout en étant assez puissants pour les transformer, puis les mettre à disposition pour l’analyse.
Ajouter des données dans Power Query
De notre côté, nous allons utiliser cet outil dans Excel.
Pour ce faire, nous allons commencer par lancer cet outil :
aller dans l’onglet "Données" ;
cliquer sur "Obtenir des données" ;
puis lancer l’éditeur Power Query.
Après quelques secondes de chargement, nous nous retrouvons avec une fenêtre comme celle ci-dessous :
Mais, il n’y a rien dans cette fenêtre ?
C’est normal, pour le moment, notre outil est vide. Cet outil s’appelle un ETL, donc nous allons commencer par la première lettre, "E" pour Extract (extraction des données).
Commençons ensemble par charger les données de notre base de données Access :
cliquer sur l’onglet "Accueil" ;
puis cliquer sur "Nouvelle source" ;
choisir "Base de données" ;
cliquer sur "Access".
Il nous suffit maintenant d’indiquer à l’outil où est notre base de données sur l’ordinateur, puis de cliquer sur "Ouvrir".
Il ne vous reste plus qu'à choisir les deux tables et à cliquer sur "OK". Ajoutons maintenant un fichier Excel contenant :
le numéro du département ;
le nom du département ;
le nom de la région ;
le regroupement.
Nous avons maintenant accès à une base de données ainsi qu’à un fichier de données directement dans Excel.
Automatiser le nettoyage des données
Nos données sont maintenant accessibles dans Power Query. Nous allons passer à la deuxième lettre d’ETL avec le "T" pour Transform (transformation). Imaginons que nous souhaitions trouver pour chaque ville quel est le département de rattachement en utilisant les données du fichier Excel.
Si nous regardons notre table “Adresse”, nous voyons que nous n’avons pas le département, mais seulement le code postal.
Nous allons donc créer une nouvelle colonne qui va récupérer les deux premiers caractères du code postal. Nous allons appeler cette colonne "Code département".
Pour ce faire nous allons :
cliquer sur la colonne “Code_Postal” :
aller dans l’onglet “Ajouter une colonne” ;
puis “Extraire” et “Plage” ;
“Index de départ” : 0 ;
“Nombre de caractères” : 2.
Nous venons de créer une colonne qui contient maintenant le code département. Nous allons renommer cette colonne “Département”.
Dans la fenêtre de droite, nous pouvons voir l’historique, les "étapes appliquées”.
Si nous cliquons maintenant sur “Navigation” dans cette fenêtre, nous pouvons voir que la colonne a disparu. Si nous cliquons à nouveau sur “Colonnes renommées”, la colonne revient.
Power Query garde en historique les différentes actions que vous avez réalisées dans le fichier.
C’est très utile pour plusieurs raisons.
La première, c’est que c’est intéressant de remonter le nettoyage et de se rappeler les différentes étapes. Nous pouvons comprendre le cheminement du nettoyage des données que nous avons mis en place et le modifier facilement.
La deuxième raison, c’est que Power Query connaît maintenant la routine de nettoyage des données que nous avons mise en place. Ainsi, si nous changeons les données dans notre base Access, nous n’aurons pas besoin de refaire le nettoyage, mais seulement d’actualiser Power Query. Il se chargera alors de refaire lui-même les mêmes étapes de nettoyage.
Il nous manque maintenant la liaison entre notre département du fichier "Adresse" que nous avons créé et le code du département dans le fichier "Département".
Nous allons faire un clic droit dans la partie “Requête”, puis choisir “Nouvelle requête”, aller sur “Combiner” et pour finir “Fusionner les requêtes comme nouvelles”.
Dans cette nouvelle interface, nous allons choisir en premier la table “Adresse”, puis la table “Département”. Pour finir, nous allons cliquer sur la clé en commun entre les deux tables : le code département.
Vous devriez avoir une fenêtre qui ressemble à cela :
Vous n’avez plus qu’à cliquer sur “OK” pour obtenir une nouvelle table qui est la fusion entre la table “Adresse” et la table “Département”.
Maintenant que notre fichier contient toutes les informations (départements et régions), nous pouvons l’utiliser sur Excel pour notre analyse.
Vous pouvez également essayer d’ajouter une nouvelle adresse dans votre base de données dans la base Access (pour plus de facilité, vous pouvez ajouter une adresse hors départements 30, 34 et 13 que nous avons déjà traités).
Vous n’avez plus qu'à repartir dans Power Query, puis aller dans “Accueil” et cliquer sur “Actualiser”, puis “Actualiser tout”. Si tout s'est bien passé, vous devriez avoir une nouvelle ligne avec votre adresse et les informations relatives au département et à la région de votre code postal.
Pour finir et pour faire le lien avec le VBA, nous pouvons maintenant demander à notre code VBA de lancer une mise à jour automatique sur nos données.
La ligne de code est assez simple pour faire la mise à jour :
ActiveWorkbook.Connections("Requête - Adresse avec département").RefreshÀ vous de jouer !
L’automatisation de votre projet est presque finie. Le directeur marketing souhaite maintenant utiliser votre fichier pour analyser les prix de vente de concurrents. Il vous a donné les prix de vente issus d’un panéliste avec, pour chaque référence, les prix de vente de six enseignes concurrentes.
Il vous demande :
d’intégrer les deux fichiers dans Power BI :
votre fichier contenant toutes les données ;
le fichier avec les prix de vente des concurrents ;
de nettoyer les fichiers Excel (format) ;
de créer une clé unique (concaténation des dates et de l’EAN) ;
de faire la jointure avec la nouvelle clé ;
de faire un graphique pour le mois de juin, montrant l’évolution de notre prix VS le prix de vente des concurrents pour l’ensemble des EAN.
Vous avez terminé ? Voici la correction en screencast :
En résumé
Power Query est un ETL qui permet d’extraire, de transformer et de mettre à disposition des données.
Il peut se connecter à plusieurs dizaines de bases de données différentes (Azure, Excel, Access, MySQL, etc.).
La séquence de nettoyage est directement enregistrée par l’outil, ce qui lui permet de refaire la séquence à chaque mise à jour des bases de données.
On peut modifier ou créer des colonnes afin de rendre les données exploitables ;
La liaison se fait comme en SQL avec des clés primaires et étrangères.
Félicitation vous avez terminé ce cours !
