Power Query : un outil au service du traitement de la donnée

Excel : un outil qui a montré ses limites

Excel est un outil formidable pour traiter des données, qui n’a de cesse d’évoluer, comme en témoignent les récentes évolutions (mise en place de formules matricielles dynamiques ou encore la mise en place de types de données avec la possibilité de requêtes sur des données boursières et géographiques).
 
Cependant, malgré toutes ses qualités, Excel a quelques inconvénients dont voici les 3 principaux :
  • L’utilisation des formules Excel
  • L’obligation de rédiger et de documenter ses travaux
  • La rigidité du langage VBA pour optimiser ses travaux
 
Détaillons cela.
 

Les formules : un outil nécessaire mais facteur de risque

Un exemple vaut mieux que 1 000 mots :

Plusieurs constats :
  • Une formule peut s’étendre sur plusieurs lignes et devenir alors difficilement lisible avec un risque d’erreur élevé
  • Hormis si une protection du classeur est activée, un utilisateur peut facilement modifier une formule et en détourner le résultat
  • Pour modifier une formule, il faut avant tout comprendre la logique de celui qui a écrit cette formule et enfin comprendre la syntaxe de la formule
  • Si on étend certaines formules conditionnelles ou complexes sur des milliers de lignes et plusieurs colonnes, le fichier devient lent et difficilement utilisable
  • Il peut y avoir des problèmes de plage de données potentielles (les formules ne prennent pas en compte toutes les données)
 
Deux types de risque d’erreurs en découlent :
  • Le risque humain : un individu commet une erreur lors de la saisie de la formule
  • Le risque machine : un bug est survenu, ce qui rend le fichier obsolète, avec des erreurs dans les formules

L’obligation de documenter ses travaux

Souvent, après avoir créé un fichier Excel, il est nécessaire de rédiger un mode opératoire afin que les utilisateurs comprennent le fonctionnement des onglets et des formules.

Exemple :

Il n’est donc pas forcément évident d’assurer une continuité en cas de départ d’un collaborateur ou si un nouveau collaborateur doit être formé sur le fichier.

Il faut également que le mode opératoire soit suffisamment détaillé pour permettre aux futurs utilisateurs de bien le comprendre. Ce n’est pas forcément tout le temps évident.

VBA : un outil d’automatisation difficile à appréhender

Sur Excel, il existe un langage de programmation qui s’appelle le VBA (« Visual basic for application »).
Ce langage permet de créer des programmes informatiques pour automatiser des actions répétitives ou effectuer un nombre important d’opérations.

Cependant, il peut devenir compliqué de devoir nettoyer ce code qui a un langage bien spécifique. Même les personnes les plus expérimentées peuvent être bloquées sur des parties de codes VBA complexes :

Il est également nécessaire, comme dans l’exemple ci-dessus, de documenter ses travaux (commentaires en vert) et de maîtriser ce langage de programmation spécifique.

La fonction « Enregistreur automatique » existe pour les débutants, mais n’est pas très efficace car elle oblige à nettoyer le code au fur et à mesure (toutes les actions, même des déplacements de souris, étant enregistrées).

Nous venons de voir qu’Excel avait certaines limites…

Mais pour répondre à toutes ces difficultés, Microsoft a intégré un nouvel outil directement dans Excel : Power Query.

 

Introduction à Power Query

Power Query est accessible directement via le volet « Données » après avoir sélectionné au préalable des données :

Vous êtes dans Power Query, tout simplement !

 

Présentation de l’interface

Power Query se découpe en trois volets :
Le volet du haut est un volet de navigation.

Le volet de gauche correspond à vos requêtes (données) chargées.
Ici une seule requête a été chargée (Tableau1) mais il est possible de charger plusieurs requêtes.

Le volet de droite correspond aux étapes appliquées.

Ici deux étapes existent déjà :

  • Source : action de départ qui consiste au chargement de vos données
  • Type modifié : Power Query a automatiquement repéré les formats et les a corrigés

Dès que vous allez réaliser une opération dans Power Query, une étape va être créée.

Contrairement à l’enregistreur de macro dans Excel, seules les actions concrètes sont enregistrées (et non tous les déplacements de souris).
Le code enregistré est donc tout de suite plus efficace et fonctionnel.

Comment utiliser Power Query ?

Power Query est également un outil au service de la donnée. Il va vous permettre de nettoyer, enrichir et transformer des données provenant de multiples sources.

L’outil permet de se connecter nativement à plusieurs applicatifs :

Comme vous le voyez, nous pouvons facilement exploiter des données :

  • Des fichiers PDF sous certaines conditions
  • Plusieurs fichiers déposés sous un même répertoire avec une même structure
  • Des données du Web (données publiques ou non cryptées)
  • Une connexion à un applicatif métier (comme un logiciel comptable ou un logiciel de gestion) afin de récupérer les données utiles à nos analyses

Toutes ces données chargées vont ensuite être retraitées sous Power Query.
Les différentes requêtes pourront ensuite être liées entre elles avec des jointures.

Il est donc possible avec Power Query d’utiliser facilement des données de différentes sources, ce qui était plus complexe sur Excel.

La restitution pourra se faire directement sur Excel ou être intégrée à des rapports plus élaborés avec Power BI.

Conclusion

Power Query est un outil complémentaire à Excel, qui permet de traiter plus facilement vos données, avec une meilleure traçabilité.
L’outil permet également de travailler facilement sur différentes sources de données.

Nous expliquerons dans une prochaine newsletter, comment fonctionne l’outil et comment travailler dessus.
Si vous souhaitez en savoir plus sur l’outil, n’hésitez pas à nous contacter.