Power Query – Regroupement des données
Par Simon-Pierre Morin, ing.
(niveau débutant)
Dans le cadre de l’Agile tour Québec 2023, j’ai présenté des solutions aux problèmes liés aux rapports Excel tels que présentés par Mathieu Boisvert.
Dans cette présentation, je faisais mention que l’un des principaux problèmes venait du fait que les informations n’étaient pas centralisées. En effet, pour faire un suivi adéquat, il est souvent nécessaire de se connecter à un ERP, un logiciel de suivit de projet (Jira, Azure DevOps ou autre), des pipelines, un système de gestion de version et j’en passe. De plus, ces différentes sources ne sont pas toujours compatibles au niveau des formats (dates, heures, nombres, encodage de texte, paramètres régionaux, langues), et ce, sans compter le fait que certains champs sont parfois du texte libre (que l’utilisateur peut entrer librement ce qu’il veut), ce qui ajoute un niveau supplémentaire de source d’erreur. De plus, la gestion manuelle d’une extraction dans Excel tend à utiliser des données défraîchies et nécessite de refaire les opérations manuellement à chaque fréquence d’actualisation du rapport.
Acquisition avec Power Query
La solution est donc d’utiliser un modèle de données en faisant l’acquisition du jeu de données de manière automatisée et centralisée en utilisant Power Query. D’autres billets suivront pour discuter d’optimisation avec Power BI, mais prendre en considération qu’il est possible d’utiliser Power Query autant dans Excel que dans Power BI. Il est donc possible de faire la transition en plus d’une étape si votre but est d’utiliser Power BI à terme.
Power Query utilise le langage M. Ce langage de programmation permet de se connecter à virtuellement n’importe quelle source de données. Plus d’une centaine de sources sont déjà disponibles, sans compter les connecteurs génériques. Après avoir établi la connexion, nous pouvons :
- demander à Power Query d’effectuer des opérations;
- formater les données afin qu’une date soit une date;
- indiquer comment gérer les erreurs;
- mettre en relation les données (ce qui sera complémenté par les outils de relations du modèle sémantique)
- Ajouter des opérations locales avec DAX (Power BI) ou VBA (Excel)
Dans le cas des connecteurs connus, la transformation de format est généralement gérée par le serveur avant de recevoir les données, mais il est toujours possible de modifier ce qui a été reçu.
Les modèles de relation de données permettent de créer des relations autrement impossibles à gérer pour ces sources disparates.
En mettant ainsi vos données dans un modèle de jeux de données et en donnant à Power Query votre logique de transformation de données (on pourrait même y incorporer une partie de l’intelligence d’affaires), vous n’aurez plus à faire ces opérations manuellement. De plus, avec un simple clic du bouton « Actualiser », vos données seront à jour, sauvant ainsi de nombreuses heures de gestion de rapport.
Dans les prochains billets, j’expliquerai de manière plus technique comment optimiser les connexions aux sources REST API et OData, comment utiliser les flux de données pour améliorer les performances générales des rapports ainsi que des trucs en M pour créer un « wrapper » de connexion pour limiter le nombre de requêtes d’authentifications. Je vous indiquerai également les pour et les contres de chacune des méthodes afin que vous puissiez prendre une décision éclairée à savoir quand utiliser l’une ou l’autre de ces astuces.
Toujours curieux?
Pour ceux qui souhaitent poursuivre et en apprendre plus, je les invite à suivre la série intitulée « Série – La création d’un tableau de bord agile à l’aide de Power BI » de Simon-Pierre Morin et Mathieu Boisvert.
