Power Query l'ETL d'Excel

Power query qu'est ce que c'est ?

Power Query est un complément d’Excel présent nativement depuis la version 2016 d’Excel.  Il était déjà disponible  en téléchargement depuis 2010. Power Query n’est donc pas une nouveauté et pourtant c’est un outil encore très méconnu. Et c’est bien dommage ! En effet, si Excel reste une application très puissante dont l’efficacité n’est plus à démontrer, il n’en reste pas moins que certaines opérations demandent la maîtrise de fonctions avancées ou la création de formules complexes et parfois très longues qui engendrent des risques d’erreur. Concernant l’automatisation des processus, il faut en général développer une macro. C’est là que Power Query peut vous venir en aide. Il fait partie de la catégorie des ETL (Extract, Transform, Load). Il permet de se connecter à des sources très diverses : fichiers, dossiers, bases de données, page web. Une fois les données importées, il permet de nettoyer, restructurer, enrichir, combiner les données grâce à une interface utilisateur. Les données ainsi transformées peuvent ensuite être chargées dans Excel.

Automatiser les traitements

Dans Excel l’automatisation des traitements demande en général la création de macros et bien souvent l’écriture d’un script VBA. La maîtrise d’un langage tel que VBA n’est pas forcément accessible à tout le monde et exige une pratique régulière. Dans Power Query, il est possible d’automatiser l’import et le traitement de nouvelles données sans saisir une seule ligne de code. Grâce aux connecteurs intégrés et aux requêtes, les données sont mises à jour à l’ouverture du fichier.

Fusionner des données de plusieurs tables

La fameuse RECHERCHEV (de plus en plus remplacée par la RECHERCHEX) qui permet de fusionner des données d’un tableau avec des données d’un autre tableau à partir d’une colonne commune est avantageusement remplacée par la fonctionnalité de fusion de requête dans Power Query. Pas besoin de formule, il suffit d’indiquer la colonne de jointure entre les deux tables et de sélectionner ensuite les colonnes à rapatrier.

traiter les colonnes texte

Power Query permet d’extraire, de fractionner, de concaténer du texte via des outils disponibles dans le ruban ou en lui montrant des exemples de ce que l’on veut obtenir. En clair, là où l’ajout d’une colonne personnalisée à partir de colonnes de texte demandait la création d’une formule complexe impliquant les fonctions GAUCHE, DROITE, STXT, NBCAR, etc, on peut avec Power Query obtenir le résultat sans forcément saisir du code.

Ajouter de colonnes conditionnelles

La fonction SI bien connue des « exceleurs » est largement utilisée et ne demande pas un niveau de maîtrise très élevée sauf si les arguments contiennent des formules complexes bien sûr. Pourtant, elle a aussi ses limites notamment quand on commence à imbriquer un nombre important de SI. La formule devient rapidement illisible et les risques d’erreurs augmentent en même temps que le nombre de Si. En effet, des parenthèses mal fermées ou un nombre incorrect d’arguments sont des erreurs classiques mais pas forcément faciles à localiser dans une formule de 4 ou 5 SI imbriqués. Et si on pouvait éviter ces SI en remplissant un formulaire dans lequel on indiquerait chaque condition, la colonne qui doit vérifier cette condition et la valeur en sortie ? Et bien c’est possible dans Power Query !

Dépivoter des colonnes

Nous devons parfois effectuer des analyses de données organisées dans un tableau à double entrées. Dans ce type de tableaux, des données de même type (des dates par exemple) sont en colonnes ce qui rend le tableau inexploitable et la génération de TCD impossible. Pire encore, les tableaux issues de sources en ligne notamment peuvent débuter par 2 lignes d’entête avec des cellules fusionnées. La modification de la structure de tels tableaux est non seulement chronophage mais parfois un véritable casse tête. Power Query permet de dépivoter facilement en quelques clics des colonnes afin de transformer un tableau à deux entrées en une table à plat prête à l’analyse.

Gagnez du temps grâce à power Query !

Voilà quelques avantages parmi d’autres dont vous pouvez bénéficier et qui vous feront gagner un temps précieux. Alors si vous êtes un utilisateur régulier d’Excel parce que c’est pour vous un outil indispensable mais que certaines manipulations vous semblent chronophages et rébarbatives, allez voir du coté de Power Query ! Accessible depuis le ruban Données d’Excel, il est déjà installé sur votre poste, pourquoi s’en priver ?