Power Query, aussi appelé Get & Transform, est un outil intégré à Excel qui vous permet d’importer des données externes, de les structurer, modifier et analyser en toute simplicité.
Dans un monde de plus en plus data-driven, bien gérer ses données s’impose comme un enjeu de taille. C’est là que Power Query trouve tout son sens, en offrant une solution efficace pour manipuler et exploiter vos données. Comment ? Voici un guide complet sur les bases de cet outil Excel.
Qu’est-ce que Power Query sur Excel ?
Power Query est un outil ETL (Extract, Transform, Load) qui permet d’extraire, de transformer et d’exploiter des données.
- Extraction des données : Power Query peut récupérer des données à partir de divers fichiers et sources externes (c’est-à-dire que contrairement à d’habitude, vos données ne sont pas déjà présentes dans le document Excel, mais vous allez les chercher ailleurs).
- Transformation des données : il offre une interface intuitive pour réaliser différentes transformations, nettoyer, remodeler et découper vos données.
- Chargement des données : une fois transformées, les données peuvent être chargées sous forme de tables, tableaux ou liaisons.
Cet outil proposé par Excel facilite l’importation et la manipulation de données via l’éditeur Power Query. Vous pouvez configurer une requête une seule fois et la réutiliser facilement par la suite, avec un simple rafraîchissement.
Mais certains utilisateurs peuvent se demander pourquoi opter pour Power Query plutôt qu’Excel pour modeler ses données ?
À première vue, les fonctionnalités d’Excel suffisent. En effet, Excel permet de gérer des données efficacement, mais Power Query se démarque lorsqu’il s’agit de traiter une grande quantité d’informations.
Lorsque vous devez nettoyer, transformer et mettre à jour régulièrement des centaines, voire des milliers de lignes, Power Query devient indispensable.
Où trouver Power Query sur Excel ?
Selon la version de Microsoft Excel que vous utilisez, Power Query peut être déjà intégré à votre interface. Sur Excel 365 et Excel à partir de 2016, vous le trouverez dans l’onglet « Données » en haut de l’écran, avec toutes les fonctionnalités accessibles à gauche (même si le terme « Power Query » n’est pas mis en évidence, c’est bien là !).
Si vous utilisez Excel 2010 ou 2013, il faut télécharger et installer le module Power Query depuis le site officiel de Microsoft. L’installation est gratuite et ne prend que quelques secondes.
Comprendre l’éditeur Power Query
Power Query dispose de son propre éditeur qui s’ouvre dès que vous importez des données ou que vous cliquez sur « Données » > « Obtenir des données » > « Lancer l’éditeur Power Query ».
- Ruban : En haut, comme dans une interface classique, vous trouvez le ruban, composé de cinq onglets, dont Accueil, Transformer, Ajouter une colonne et Affichage. Chaque onglet regroupe des actions spécifiques.
- Aperçu des données : Situé au centre de l’interface, c’est ici que vos données sont affichées. Vous pouvez suivre en temps réel les modifications apportées après chaque étape de vos requêtes.
- Barre de formule : Comme sur l’interface classique d’Excel, la barre de formule affiche l’action en cours d’exécution appliquée à des cellules, lignes ou colonnes. Vous pouvez l’utiliser pour modifier directement la formule.
- Requêtes : Cette section, à gauche de l’éditeur, vous donne accès aux requêtes enregistrées sur votre classeur. Si vous vous demandez comment faire une requête sur Excel, nous vous expliquons tout plus bas dans l’article.
- Propriétés : À droite de l’interface, cette section affiche les propriétés de la requête sélectionnée, comme son nom, sa description ou ses paramètres. C’est notamment ici que vous pouvez nommer une requête en toute simplicité.
- Étapes Appliquées : Située sous la section « Propriétés », cette zone affiche l’historique des étapes effectuées dans vos requêtes. Ajouter, supprimer, modifier, vous pouvez y changer les étapes, pour créer des requêtes qui s’adaptent à vos besoins.
L’éditeur avancé est une interface textuelle qui permet de coder directement depuis Power Query. Le langage utilisé est le langage M, propre à Microsoft.
Pour y accéder, cliquez sur « Éditeur avancé » en haut à gauche de l’interface Power Query.
Depuis la fenêtre qui s’ouvre, vous pouvez modifier librement le code d’une requête sélectionnée.
Utiliser Power Query : tutoriel étape par étape
Importer les données
Tout d’abord, la première étape pour exploiter vos données avec Power Query consiste à les importer depuis une source externe. Power Query prend en charge de nombreuses sources différentes, dont trois disponibles directement depuis l’onglet « Données » d’Excel :
- À partir d’un fichier texte/CSV : si vos données sont stockées dans un fichier .csv.
- À partir du web : si les données proviennent d’un site web.
- À partir d’un tableau ou d’une plage : si vos données se trouvent dans un tableau Excel ou une plage de cellules.
Mais Excel permet également d’importer et de connecter des données à partir de plusieurs autres sources.
Pour y accéder, cliquez sur « Obtenir des données » et sélectionnez l’option qui répond à vos besoins. Il est possible de connecter et d’importer des fichiers PDF, XML, JSON, des bases de données SQL et même des sources Azure.
Dans notre exemple, nous allons importer un fichier CSV en ligne. Nous sélectionnons donc l’option « Importer à partir du web », puis collons l’URL de la source.
Une fenêtre de confirmation apparaît. Il suffit ensuite de cliquer sur « Transformer les données » pour que l’éditeur Power Query se lance avec les données chargées.
Les dernières études montrent que Microsoft Excel est utilisé par plus de 40% des entreprises. Si vous êtes salarié, pourquoi ne pas profiter de votre solde CPF pour suivre une formation Excel certifiante ? Grâce à des formations de choix, vous pouvez améliorer vos compétences sur Excel et amener votre carrière à un autre niveau !
Transformer ses données
Vous pouvez désormais transformer vos données à volonté. À partir des onglets Accueil, Transformer, Ajouter une colonne, Affichage, vous avez accès à une multitude d’actions :
- Trier et filtrer les lignes et les colonnes.
- Ajouter, supprimer ou dupliquer des colonnes.
- Éliminer les données inutiles (lignes en double, données obsolètes, caractères supplémentaires…).
- Remplacer ou supprimer certaines valeurs.
- Diviser une colonne en plusieurs segments.
- Regrouper des données par catégories et y appliquer des calculs.
- Utiliser des fonctions.
Et c’est là où les requêtes entrent en jeu !
Comprendre les requêtes Power Query
Une requête est une séquence d’instructions définie par l’utilisateur. Toute la puissance de Power Query repose sur la notion de requête, car elle permet non seulement de manipuler et de transformer les données, mais surtout d’automatiser les tâches complexes.
Pour créer une requête Excel, il suffit d’effectuer une série d’actions dans l’interface de Power Query (comme celles citées plus haut), qui génère automatiquement une requête associée à ces actions. Vous pouvez ensuite la renommer, modifier les instructions ou la mettre à jour.
À noter qu’il existe également des requêtes SQL : des instructions SQL qui s’intègrent directement lors de l’importation de données depuis une base de données (SQL Server, MySQL, etc.). Ainsi, il est possible d’écrire une requête SQL pour filtrer et structurer ses données avant même leur chargement dans Power Query. Si le sujet vous intéresse, consultez notre formation SQL en ligne !
Ajouter ou fusionner plusieurs sources
L’une des fonctions les plus appréciées sur Power Query est la capacité à pouvoir ajouter ou fusionner les données de plusieurs requêtes. Mais pourquoi est-ce si intéressant ?
Car chaque requête crée une table de données propre.
Cela signifie que les données ne restent pas identiques d’une requête à l’autre, car chacune les module en fonction des actions préalablement définies.
1 requête = 1 tableau de données.
Et il arrive souvent d’avoir besoin de joindre deux requêtes, c’est-à-dire deux tableaux de données.
- L’ajout permet d’ajouter des lignes d’une table à une autre, à condition qu’elles contiennent les mêmes colonnes :
- La fusion permet de joindre deux tables en fonction d’une ou plusieurs colonnes communes.
Vous pouvez fusionner ou ajouter plusieurs requêtes à partir de l’interface Excel ou de l’éditeur Power Query.
- Sur Excel, cliquez sur « Données », puis sur « Obtenir des données » et « Combiner » pour choisir l’option souhaitée.
- Dans Power Query, cliquez sur « Combiner » en haut à gauche.
Charger ses données sur une feuille Excel
Une fois vos transformations terminées, n’oubliez pas que votre source d’origine reste inchangée. Vous avez modifié et exporté vos données via Power Query. Pour les charger dans Excel, c’est-à-dire les transférer de l’interface Power Query vers un fichier Excel, deux options s’offrent à vous.
Dans l’onglet Accueil en haut à gauche, cliquez sur « Fermer et charger » :
- Fermer et charger : charge vos données dans l’emplacement par défaut, généralement une nouvelle feuille Excel.
- Fermer et charger dans… : vous permet de choisir l’emplacement de destination (classeur, tableau croisé dynamique, etc.).
Actualiser et automatiser le rafraîchissement de Power Query
Lorsque vous actualisez une requête, chacune des étapes qui la composent s’exécute automatiquement sur vos données. Et c’est là que réside la capacité d’automatisation de Power Query : vous avez la possibilité d’appliquer vos requêtes personnalisées à de nouvelles bases de données, et ce, en un clic.
En d’autres termes, vous pouvez enregistrer une série d’actions qui s’exécutent automatiquement dès que vous les appliquez à une nouvelle source de données.
Par exemple, imaginons que vous créez une requête intitulée « MAJ_Hebdomadaire » avec une série d’actions que vous souhaitez appliquer chaque semaine à vos données.
Après avoir chargé vos données, vous pouvez retrouver la requête en cliquant sur « Données », puis « Requêtes et connexions ».
Cette fenêtre, qui s’affiche à droite de l’écran, vous présente toutes vos requêtes que vous pouvez utiliser en cliquant simplement dessus.
Vous pouvez également automatiser l’actualisation de vos données, sans avoir à ouvrir le fichier source. Pour cela, il suffit de programmer l’application automatique d’une requête à vos données :
- Cliquez sur « Données », puis sur « Requêtes et connexions ».
- Dans le volet, faites un clic droit sur la requête souhaitée et choisissez « Propriétés ».
- Cochez la case « Actualiser toutes les… » et indiquez la période voulue.
- Cliquez sur « OK » pour enregistrer les paramètres.
Maitrisez Power Query grâce à une formation Excel sur-mesure
Comprendre et utiliser Power Query donne une nouvelle dimension à votre gestion et analyse de données. Chez Clic Compétences, nos formateurs certifiés vous offrent une formation Excel ultra-personnalisée, adaptée à votre niveau et à vos besoins.
Que vous choisissiez nos cours en ligne ou en présentiel, vous bénéficiez d’un suivi individualisé pour progresser à votre rythme et tirer le meilleur parti d’Excel. Inscrivez-vous dès maintenant !
Autres articles concernant le Compte Personnel de Formation
