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.

C'est là que Power Query excelle, et ce 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.

Cet article met en lumière 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

‍

#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 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.

‍

Conclusion

‍

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.

‍