Découvrez Power Query : Définition, concepts et mode d'emploi pour débutants

Utiliser Excel aujourd'hui est bien différent d'il y a 5 ans. Au-delà des évolutions apportées par Microsoft, les besoins se sont diversifiés et complexifiés. Auparavant, nous nous contentions de quelques recherches verticales et tableaux de synthèse, avec des données saisies manuellement ou simplement copiées-collées. Désormais, le véritable défi consiste à manipuler de grands volumes de données provenant de sources diverses.

‍

Comment installer Power Query ?

Bonne nouvelle : dans 90 % des cas, Power Query est déjà là, tapi dans l'ombre, prêt à l'emploi.

Power Query sur Windows

• Versions 2016, 2019, 2021 et Microsoft 365 : Rien à installer ! Allez simplement dans l'onglet Données > groupe Récupérer et transformer les données. C'est ici que la magie opère.
• Versions 2010 et 2013 : Power Query est un "invité". Vous devez télécharger et installer le complément (add-in) gratuit sur le site de Microsoft. Une fois installé, un nouvel onglet "Power Query" apparaîtra dans votre ruban.

Power Query sur Mac

Longtemps attendu, il est enfin là (depuis la version 16.29).

• Direction l'onglet Données > Obtenir des données (Power Query).
• Note : Les fonctionnalités sont encore un peu plus limitées que sur Windows, mais l'essentiel est là !

Power Query sur le web

L'outil s'est démocratisé : si vous travaillez sur navigateur, vous le trouverez sous l'onglet Données > Obtenir des données. Idéal pour des nettoyages rapides dans le cloud.

Power Query dans Power BI

Dans Power BI Desktop, Power Query est le cœur du réacteur. Power Query est intégré nativement dans Power BI. Pour l'ouvrir, rendez-vous sur l'onglet Accueil et cliquez sur Transformer les données.

Pourquoi utiliser Power Query ?

Power Query excelle pour plusieurs raisons :

1. ‍Si vous avez Excel, vous avez Power Query (sur Windows comme sur MacOS) : aucun complément ni installation supplémentaire nécessaire.
2. C'est l'outil qui permet d'extraire les données depuis diverses sources : un autre fichier Excel ? Facile. Un site internet ? Tout aussi simple. API ? SharePoint ? Un dossier entier ? Aucun problème.
3. Les données brutes sont rarement structurées de façon optimale pour Excel. C'est ici que Power Query révèle son second atout : il peut transformer les données pour obtenir des tableaux faciles à utiliser et correctement structurés.
4. Une fois l'extraction et la transformation effectuées, le lien entre les sources et le fichier reste actif. Ainsi, quand les données sources sont mises à jour, nul besoin de tout recommencer — il suffit de cliquer sur le bouton d'actualisation dans Excel.

Découvrez ici 3 exemples simples d'utilisation de Power Query :

• Connecter 3 fichiers Excel différents et combiner des données
• Importer des données stockées en ligne sur un Google Sheets pour les analyser dans Excel
• Comment extraire un tableau d'informations (comme une liste de pays et leur démographie) directement depuis une page Wikipédia.

Voici 5 concepts de Power Query à intégrer à vos classeurs sans plus attendre :

• Connectivité aux diverses sources de données
• Transformations étape par étape
• Disponibilité de nombreux outils permettant d'effectuer des transformations sans coder
• Le langage M, pour les utilisateurs les plus aguerris
• Les requêtes actualisables en un clic

Et comme nous sommes fans de Power Query chez Solpedinn, nous vous avons listé les 5 raisons de se former sur Power Query !

Comment utiliser Power Query sur Excel ?

#1 | La connectivité

‍

Comme on peut le constater, Power Query offre de nombreuses possibilités d'import de données, utilisables tant sur Excel que sur Power BI. Parmi celles-ci : l'import depuis un autre fichier Excel, un dossier entier, un site SharePoint, le web, une API…

‍

Mieux encore, en créant plusieurs requêtes, vous pouvez obtenir des données de sources multiples, identiques ou différentes, simultanément. Cette fonctionnalité s'avère particulièrement utile pour combiner les requêtes en un seul tableau final.

‍

Il ne s'agit donc pas simplement de connecter des classeurs entre eux, mais bien de relier votre classeur à diverses sources externes.

‍

#2 | Transformations étape par étape

‍

Pour être efficaces, les étapes de transformation sur Power Query doivent être logiques et claires. Pourquoi ? Parce que chaque transformation s'applique au résultat de la précédente. En d'autres termes, chaque étape dépend de toutes celles qui la précèdent.

‍

Il convient donc de réfléchir en amont à ce cheminement logique, au moins dans ses grandes lignes.

‍

L'avantage est que toutes les étapes sont réversibles en un clic en cas d'erreur. Attention toutefois à la suppression des étapes intermédiaires, qui peut rendre les étapes suivantes inutilisables. D'où l'importance de définir chaque étape clairement : plus elles sont précises, plus le cheminement est compréhensible et les modifications aisées.

‍

#3 | Possibilité de ne pas coder

‍

Écrire du code est accessible à tous ceux qui le souhaitent dans Power Query. Pour les autres, le logiciel propose des outils de transformation en un clic couvrant la majorité des besoins.

‍

Bien sûr, ces manipulations peuvent s'avérer limitées pour des cas complexes, mais même alors, l'utilisation du code (en langage M) peut se restreindre à une ou deux étapes seulement.

‍

Outre leur diversité et leur puissance, ces outils sont remarquablement intuitifs. La plupart du temps, nous travaillons avec seulement trois onglets : Accueil, Transformer et Ajouter une colonne.

‍

À chaque étape, la question est simple : « Dois-je transformer une colonne ou un tableau existant, ajouter une nouvelle colonne, ou utiliser un outil basique ? » Selon la réponse, je sélectionne l'onglet approprié. Pour Transformer ou Ajouter une colonne, je me demande ensuite : « Quel type de données je souhaite ajouter ou traiter ? » La réponse facilite la localisation de l'outil adéquat.

‍

#4 | Le langage M

‍

C'est l'atout qui élève les transformations au niveau supérieur. L'utilisation du langage M — dans une colonne personnalisée, une fonction personnalisée ou l'éditeur avancé — permet d'affiner considérablement les transformations. La contrepartie étant qu'il faut alors passer par du code.

‍

Ce langage offre également un contrôle accru, permettant d'optimiser les étapes tant en clarté qu'en performances — un enjeu crucial lorsqu'on traite d'importants volumes de données.

‍

Enfin, les outils intégrés ont tendance à « hard coder » les références, c'est-à-dire à les appeler directement sans passer par des variables, les rendant ainsi figées et difficilement réutilisables. Là encore, des modifications dans l'éditeur avancé peuvent rendre la requête plus dynamique.

‍

Voici un exemple du code généré, partiellement par des outils, et partiellement codé directement dans l'éditeur avancé :

#5 | L'actualisation des requêtes

‍

Bravo, vous avez réussi à importer vos données et à leur appliquer toutes les transformations nécessaires. Que se passe-t-il ensuite ?

Nous arrivons à la dernière étape : le chargement des requêtes. Plusieurs options s'offrent à vous : créer un tableau dans une nouvelle feuille, établir simplement une connexion sans import direct (utile quand la requête est déjà utilisée ailleurs), ou envoyer les données vers Power Pivot pour les intégrer au modèle de données.

Le grand avantage ? Lorsque la source de données est mise à jour, nul besoin de recommencer toutes les manipulations — il suffit d'actualiser le fichier pour que tout soit pris en compte instantanément.

Formation Power Query pour aller plus loin

Quel que soit le niveau de la personne qui l’utilise, Power Query offre des possibilités intéressantes quant à l’import et la structuration des données et facilite énormément les éventuels calculs et autres synthèses par la suite sur Excel.

De plus, l’investissement d’heures dans la maîtrise de ce logiciel ne donne aucun rendement dégressif sur le long terme, bien au contraire. Ainsi, une requête qui tourne bien et qui comporte des étapes bien précises a le potentiel d’économiser des heures de retraitement manuel et d’écriture de formules hermétiques que l’utilisateur lui-même ne peut pas corriger en cas d’erreur.

‍