Une formation Power Query Excel permet d'apprendre à utiliser l'extension de business intelligence pour Microsoft Excel. Cet outil sert à importer, transformer et nettoyer les données pour les préparer avant l'analyse. Découvrez comment suivre un cursus pour devenir expert !
De nombreux professionnels sont amenés à combiner des données provenant de multiples sources, à des fins d’analyse ou de reporting. Or, les tâches de chargement et de nettoyage des données peuvent vite s’avérer fastidieuses.
L’outil Power Query de Microsoft permet d’automatiser ces processus en un seul clic. Il est disponible sur la plateforme de Business Intelligence Power BI, mais aussi sur le célèbre tableur Excel.
Qu'est-ce que Power Query sur Excel ?
Power Query est un outil de Business Intelligence (informatique décisionnelle) disponible sur le logiciel Microsoft Excel. Il permet d’automatiser le processus ETL : Extraction, Transformation et Loading (chargement) des données.
Ce processus consiste à récupérer les données depuis une source, à les transformer, puis à les stocker avant de les analyser. L’ETL est une étape essentielle dans la construction d’une Data Warehouse ou même dans la production d’un rapport hebdomadaire ou mensuel.
Il s’agit d’une application complète de transformation et préparation des données. L’utilisateur peut obtenir des sources à partir de sources en utilisant une interface graphique, et appliquer des transformations avec le Power Query Editor. Une requête peut être configurée une fois, puis réutilisée plus tard avec un simple rafraîchissement.
Ce puissant outil d’automatisation est disponible sur Excel depuis la version 2010. Les utilisateurs peuvent donc importer des données sur Excel depuis diverses sources externes, telles que des fichiers textuels, des documents CSV, des pages web ou des workbooks Excel.
Les données peuvent ensuite être nettoyées et préparées selon les besoins. Power Query offre de nombreuses fonctionnalités très utiles, comme la fusion entre divers datasets ou la synthétisation de données. C’est donc un outil très polyvalent pour la préparation de données.
À quoi sert Power Query sur Excel ?
Power Query permet à n’importe qui de transformer les données en informations pertinentes avec Excel. Grâce à cet outil, les processus de Business Intelligence ne dépendent plus d’une équipe de spécialistes IT.
Les utilisateurs d’Excel peuvent effectuer une large variété de tâches du processus ETL à l’aide de Power Query. Tout d’abord, cet outil permet la connexion instantanée avec une large variété de sources de données.
De nombreux formats sont pris en charge, donc CSV, XML, JSON ou PDF. De même, les principales bases de données comme Azure SQL Database, IBM DB2, Oracle ou PostgreSQL sont compatibles.
Par ailleurs, après la connexion à une source de données, Power Query permet de transformer les données afin de les préparer pour l’analyse. De la plus simple transformation à la restructuration de données plus complexe, quelques clics suffisent pour compléter l’opération.
De nombreuses procédures de transformation sont prises en charge. Le Data Cleaning ou nettoyage de données consiste à supprimer les doublons, à changer le type et le format des données et à organiser les tableaux.
L’intégration de données vise à combiner ou séparer des tableaux de sources, ou à agréger les données. L’enrichissement de données permet d’étendre les données sources en créant des colonnes calculées.
Il ne s’agit que de quelques exemples des possibilités offertes par Power Query pour transformer les données ! Le véritable point fort est que ces opérations peuvent être automatisées à l’aide d’une interface no-code, sans avoir à coder en VBA.
Enfin, après avoir nettoyé les données, Power Query permet de les charger vers une feuille de tableur ou vers le Data Model. Par défaut, les données sont directement transférées vers une nouvelle feuille de tableur au sein du fichier Excel. Il est possible de placer les données de chaque source dans une feuille séparée, puis de les manipuler comme n’importe quelles données Excel.
De son côté, le Data Model permet de compresser les données et de les stocker en mémoire. Cette option permet de travailler avec des centaines de millions de lignes de données, et de dépasser la limite fixée à 1 048 576 lignes par feuille de tableur Excel.
On utilise normalement ce Data Model comme base pour un tableau croisé dynamique. C’est la raison pour laquelle on l’appelle aussi le « Power Pivot Data Model ».
Pourquoi utiliser Power Query Excel ?
Le principal avantage de Power Query est de simplifier l’obtention et la transformation des données. De plus, cet outil permet d’enregistrer toutes les étapes du processus pour les reproduire par la suite.
Il est par exemple possible d’importer à nouveau les données de source, puis de ré-appliquer le filtrage, le tri et les autres transformations appliquées en un seul clic. Il n’est donc pas nécessaire de recréer tout le processus à chaque opération. En revanche, l’utilisateur peut modifier chaque étape ou en ajouter de nouvelles.
Un autre point fort de Power Query est sa simplicité d’utilisation par rapport aux autres outils de Business Intelligence. Son interface est intuitive, et très similaire à celle d’Excel.
De manière générale, Power Query est l’outil idéal pour traiter de larges volumes de données, pour importer les données de différentes sources, et pour manipuler, nettoyer et combiner plusieurs tableaux, fichiers ou dossiers.
Qui utilise Power Query Excel ?
Power Query peut être utilisé par les analystes et toute personne travaillant avec Excel, afin d’améliorer massivement leur capacité à manipuler les données. Plutôt que de mettre à jour les modèles manuellement toutes les semaines ou tous les mois, les analystes peuvent automatiser les inputs et se focaliser sur leur travail de fond.
En outre, tout analyste utilisant Excel et s’intéressant à la Business Intelligence peut commencer par Power Query. La transformation des données est en effet une étape essentielle du processus d’informatique décisionnelle. Cet outil intuitif a l’avantage de reprendre l’environnement bien connu d’Excel, et apporte des compétences pouvant être transférées vers Power BI.
Même sans utiliser Excel ou Power BI comme outil principal, quiconque travaille avec les données peut utiliser Power Query pour manipuler les données ou combiner rapidement des fichiers pour d’autres projets. Les résultats peuvent être chargés vers une feuille de tableur, et utilisés pour le prototypage rapide d’un projet BI avec des données propres.
Power Query sur les différentes versions d'Excel
À l’origine, Power Query pour Excel fut lancé comme une extension à télécharger et installer pour Excel 2010 et 2013. Après avoir ajouté Power Query à Excel, un nouvel onglet apparaissait dans le ruban Excel.
Par la suite, cet outil a été entièrement intégré à Excel 2016. Il suffit de se rendre dans la section « Obtenir et transformer » de l’onglet Data.
Si vous utilisez les dernières versions de Excel, Power Query y est donc nativement intégré. C’est aussi le cas pour Excel 2019/2021, et pour Office 365.
Power Query vs Power BI : quelle est la différence ?
Power BI est la plateforme complète de Business Intelligence de Microsoft. Elle regroupe de nombreux outils dont Power Query, Power Pivot et Power View. Cette solution permet d’effectuer toutes les étapes du processus d’analyse de données, de l’extraction de données à la création de visuels sur un tableau de bord interactif.
L’outil Power Pivot est souvent utilisé conjointement à Power Query. Il s’agit d’une autre extension Microsoft Office, permettant de créer des modèles de données à partir de tableaux préalablement nettoyés par Power Query. Les bases de données de plusieurs millions de lignes peuvent ainsi être analysées.
En outre, Power Query peut facilement remplacer le langage VBA (Visual Basic for Applications) de Power BI. Il permet de traiter les tableaux sans avoir à coder, de visualiser les opérations étape par étape sans exécuter de macro, et d’éditer l’ordre des étapes à l’aide d’une interface glisser-déposer.
Comment obtenir des données sur Power Query Excel ?
Pour consulter la liste de sources de données Power Query, rendez-vous sur l’onglet Data et cherchez Power Query dans la section « Obtenir et transformer les données ».
Cliquez sur le bouton « Obtenir les données », et vous verrez un menu déroulant apparaître. Dès lors, vous pouvez sélectionner la source de données de votre choix. Notez que la liste de sources de données Power Query disponibles dépend de la version d’Excel que vous utilisez.
Vous pouvez choisir des fichiers Excel, TXT/CSV, XML, JSON ou PDF, ou récupérer des données depuis une base de données SQL Server, Access, Oracle, DB2, MySQL, PostgreSQL, Sybase, Teradata ou SAP Hana.
Depuis le cloud Azure, il est possible de récupérer des données à partir de Azure SQL Database, Azure Synapse Analytics, Azure HDInsight (HDFS), Azure Blob, Azure Table ou Azure Data Lake Storage.
Différents services en ligne sont également compatibles dont Sharepoint Online, Exchange Online, Dynamics 365, Salesforce Objects et Salesforce Reports. Au total, Power Query offre des connecteurs pour plus de 40 sources de données.
Si vous recherchez une source de données indisponible sur Power Query, vous pouvez aussi utiliser un connecteur de données générique comme OData Feed, OLE DB ou ODBC.
Le chargement de données sur Power Query Excel
Comme évoqué précédemment, Power Query propose deux options distinctes pour le chargement de données : feuille de tableur et Data Model.
Si vous souhaitez charger vos données vers une feuille de tableur, plusieurs options s’offrent à vous. Vous pouvez charger les données vers un tableau Excel, ou vers un tableau croisé basé sur les données de source. Il est aussi possible de créer une connexion vers les données sans pour autant les charger immédiatement.
L’utilisateur peut aussi choisir entre la création d’un tableau de données, d’un tableau croisé dynamique, ou encore d’un graphique sur une feuille de tableur nouvelle ou existante. Pour ajouter les données au Data Model, il suffit de cocher la case correspondante à cette option.
Le Power Query Editor
Le Power Query Editor est une interface séparée d’Excel. Toutes les transformations de données ont lieu dans cet éditeur, pouvant être lancé de deux façons différentes.
- La première méthode consiste à cliquer sur le bouton « Obtenir des données », puis à choisir l’option « lancer l’éditeur Power Query ».
- La deuxième approche est d’effectuer un double clic sur une requête listée dans le panneau « requêtes et connexions ».
Cet éditeur Power Query se compose de six éléments principaux. Tout d’abord, le ruban se compose de cinq onglets principaux : fichier, accueil, transformer, ajouter colonne, et voir.
La liste de requête est un panneau contenant toutes les requêtes ayant été ajoutées au workbook actuel. Il est possible de naviguer vers n’importe quelle requête à partir de cet emplacement pour commencer à les éditer.
La Data Preview ou prévisualisation des données est un espace permettant de voir un échantillon des données pour une requête sélectionnée. La Formula Bar ou barre de formule présente le code M de l’étape de transformation actuelle.
Chaque étape de transformation est enregistrée dans ce code M, mais il n’est généralement pas nécessaire d’utiliser ce langage de programmation directement.
La rubrique « propriétés » permet de voir et d’éditer les propriétés d’une requête. Il est par exemple possible de la renommer, d’ajouter une description, ou d’activer le chargement rapide de données. Enfin, un espace contient la liste de toutes les étapes appliquées pour transformer les données.
Comment suivre une formation Power Query Excel ?
La maîtrise de Power Query sur Excel est une compétence très utile. À l’heure où les données constituent une précieuse ressource en entreprise, la Business Intelligence en self-service offre de nombreuses possibilités.
Afin d’apprendre à manier cet outil, vous pouvez vous tourner vers DataScientest. Notre formation Excel s’effectue en mode BootCamp sur trois à six jours en fonction du niveau choisi (débutant ou avancé), et inclut le passage de l’examen TOSA reconnu par l’État.
Le programme couvre les différentes fonctions Excel, les tableaux croisés dynamiques, les formules, les macros, la consolidation ou encore le langage Visual Basic. Vous apprendrez à gérer les feuilles de calcul, à créer des graphiques et à analyser les données.
Notre formation Power BI s’effectue en 2 à 5 jours en fonction du niveau (débutant ou avancé) et permet d’obtenir la certification officielle Power BI Data Analyst Associate.
Vous découvrirez l’univers de la BI et les fonctionnalités de Power BI, notamment Power Query. La DataViz, le langage M et les fonctions DAX sont aussi au programme.
À l’issue du parcours, vous serez capable d’analyser les données brutes, de modéliser les données transformées, ou encore de créer un tableau de bord avec plusieurs axes d’analyse avec Power BI.
Ces deux formations s’effectuent à distance via le web et sont éligibles au Compte Personnel de Formation pour le financement. Tous nos professeurs ont le statut Microsoft Certified Trainer. N’attendez plus et découvrez DataScientest !
