Power Query, un allié de choix pour Excel

En décembre dernier, nous avons accueilli le Club Power BI dans nos bureaux pour un Meetup dédié à Power Query dans Excel.

Le Club Power BI est le groupe d’utilisateurs francophones de Microsoft Power BI. Ses membres se réunissent chaque mois pour échanger sur Power BI, ses fonctionnalités, son utilisation métier et ses nouveautés.

Après une présentation des nouveautés Power BI du mois par Tristan Malherbe, fondateur du Club et MVP DataPlatform (Most Valuable Professional), trois de nos Consultants ont présenté les principaux avantages de Power Query dans Excel, illustrés par un cas client.

Pourquoi parler d’Excel aux membres du Club Power BI ?

Chez Finance 3.1, nous développons des outils de reporting sur Power BI, mais beaucoup de clients préfèrent encore rester dans l’environnement Excel, pour différentes raisons :

• Ils maîtrisent Excel et apprécient sa flexibilité
• Leurs processus existants nécessitent des analyses dans Excel
• Ils veulent conserver un environnement « on premise » (données hébergées sur les serveurs de l’entreprise) et refusent de placer leurs données sur le Cloud de Microsoft, ce qui est automatique lorsque l’on publie un rapport Power BI, à moins d’investir dans un serveur Power BI Report Server spécifique.

Or, Power Query, l’éditeur de requêtes de Power BI, existe nativement dans Excel depuis 2013.

Cet éditeur reste peu exploité par les utilisateurs Excel et souvent méconnu, alors que sa puissance de consolidation et traitement peut être intégrée à des outils 100% Excel, y compris sur des grosses volumétries de données qu’il ne serait pas possible de gérer autrement dans Excel.

Nous avons donc présenté au Club Power BI les principaux avantages de Power Query dans Excel au travers d’un un cas client. Le département Trésorerie d’une grande entreprise avait besoin d’un outil de reporting Excel permettant à l’utilisateur, tous les mois, de :

• Consolider des données issues de multiples sources externes : 20 sous-portefeuilles de placements dans différentes devises, en format CSV, à agréger en un portefeuille global, ainsi qu’une table de taux de change à récupérer depuis une page web
• Retraiter ces données et passer des ajustements manuels (ex : modifier une donnée erronée)
• Restituer des analyses de ces données retraitées et ajustées (calculs, tableaux et graphiques)

Consolider, retraiter et restituer des données : comment Power Query peut faire d’Excel une alternative sérieuse à Power BI ?

Consolider des données avec Power Query dans Excel

Une des principales forces de Power Query réside dans ses très nombreux connecteurs « natifs » à des sources de données externes (SQL Server, Azur, fichiers Excel, PDF, CSV, dossiers SharePoint, Web…)

Alors que la connexion à ces différents types de source peut être laborieuse voire impossible en VBA, Power Query permet de récupérer ces données en 3 clics. Dans notre exemple de reporting financier, nous pouvons connecter Power Query aux 20 sous-portefeuilles de placements au format CSV ainsi qu’à une page web pour récupérer des taux de change et les utiliser pour les calculs du portefeuille global.

Après s’être connecté aux données sources, Power Query se révèle extrêmement puissant pour les consolider et les retraiter. Transformer plusieurs sources en une seule table, garder uniquement les colonnes pertinentes et calculer des colonnes supplémentaires sur plusieurs milliers de lignes. Ce sont des actions de requêtage typiques pour lesquelles beaucoup de macros VBA ne peuvent être remplacées par Power Query pour deux raisons principales :

• La performance : dans notre exemple, Power Query consolide 20 fichiers CSV environ 6 fois plus vite qu’une macro qui exécute les mêmes transformations.
• La lisibilité/maintenabilité : la visualisation « par étapes » dans Power Query permet une compréhension plus simple des transformations appliquées à chaque fichier. Cette visualisation permet d’identifier plus simplement les sources d’erreurs potentielles et leur correction.

Retraiter les données grâce à Power Query dans Excel

Depuis Excel, l’utilisateur peut visualiser ses requêtes de 3 manières :

• Dans une Table, si le nombre de lignes n’est pas trop élevé
• Dans un Tableau Croisé Dynamique (PivotTable), ce qui fonctionne avec plusieurs centaines de milliers de lignes
• Dans un Graphique Croisé Dynamique (PivotChart)

La donnée va donc de Power Query vers Excel. Dans l’autre sens, l’utilisateur peut également envoyer des informations d’Excel vers Power Query, en important dans ses requêtes des tables ou des cellules du fichier Excel en question. Pour se faire, il suffit de se rendre dans l’onglet Données > obtenir des données > à partir d’autres sources > à partir d’un tableau ou d’une plage.

C’est précisément cet aller-retour entre Excel et Power Query qui permet de développer des outils de reporting « interactifs ». Cette interaction a deux intérêts :

• Intégrer aux requêtes des paramètres que l’utilisateur saisit dans des cellules du ficher Excel (Ex : date d’analyse et périmètre du portefeuille pour supprimer les placements inutiles à l’analyse du mois en question).
• Faire du « write-back » dans les sources: l’utilisateur peut modifier un montant spécifique en saisissant la nouvelle valeur dans une cellule prévue à cet effet. (Ex : modifier un taux de change ou modifier la valeur d’un placement qui est fausse dans l’export)

C’est donc en associant la capacité de saisie d’Excel avec la capacité de traitement de Power Query qu’on permet à l’utilisateur d’interagir avec ses données tout en restant dans un seul outil.

Notons qu’une telle interaction dans un rapport Power BI (modification de paramètre et write-back) ne pourrait se faire qu’en intégrant tous les éléments de la Power Platform de Microsoft (Power Apps + Power Automate + Power BI), environnement encore méconnu des utilisateurs métiers.

Pour que cette interaction soit efficace dans Excel, l’utilisateur doit pouvoir maîtriser le rafraîchissement de Power Query. Chaque aller-retour implique en effet un rafraîchissement des requêtes, pour constater l’impact des modifications que l’utilisateur a faites.

Si Excel ne propose qu’un rafraîchissement global ou par requête, VBA communique très bien avec Power Query, et permet de gérer les rafraîchissements de manière fine (ex : rafraîchir un groupe de requêtes) et de les intégrer à des macros, éventuellement au sein d’un processus automatisé plus large.

Restituer la donnée via Power Query dans Excel

Une fois que les données ont été importées et retraitées, l’utilisateur peut créer les outputs qu’il souhaite dans Excel : calculs en formules, TCD (Tableaux Croisés Dynamiques), tables reformatées, etc… L’utilisation de Power Query dans Excel plutôt que de Power BI peut alors se justifier, pour deux raisons principales :

• Le manque de flexibilité dans les visuels Power BI est un enjeu dans le développement de nos rapports. Quand les outputs attendus sont principalement des tableaux et que la structure des données permet difficilement de produire des matrices dans Power BI, utiliser les formules Excel sur des requêtes Power Query est un réel avantage. Cela implique tout de même de pouvoir charger la requête dans une table Excel. Dans la restitution de notre portefeuille de placements, par exemple, des hiérarchies imparfaites entre sous-portefeuilles et portefeuilles peuvent rendre la construction de matrice extrêmement laborieuse. Au contraire, avec les formules Excel, l’utilisateur peut construire ses tableaux de restitutions de manière totalement personnalisée.
• Le besoin de génération d’exports est aussi la bête noire des Consultants Power BI, car générer des exports à partir de requêtes Power Query depuis Power BI est, pour le moment, impossible. Or, nos clients ont souvent besoin d’exporter les bases de données retraitées dans Power Query, pour les injecter dans leurs systèmes de comptabilité par exemple. Dans Excel, VBA est parfaitement compatible avec Power Query pour générer ces exports, en CSV ou en PDF par exemple.

Enfin, l’alliance de Power Query et d’Excel pour construire des restitutions sur des requêtes est d’autant plus intéressante grâce à l’existence de Power Pivot dans Excel. Avec Power Pivot, l’utilisateur peut faire des relations entre ses requêtes Power Query et créer des mesures en DAX, tout comme sur Power BI. Cela permet ainsi de construire un modèle de données (requêtes + relations + mesures), comme sur Power BI, tout en restant dans Excel.

En somme, Power Query est un excellent allié d’Excel pour construire des outils de reporting performants, interactifs et flexibles tout en restant dans un environnement maîtrisé par les utilisateurs.

Pour en savoir plus sur Power Query dans Excel nous vous conseillons de lire l’article suivant en anglais :

Power Query: the 2020 Definitive Beginners’ Guide

Pour visionner la présentation du Meetup Power BI, cliquez ici.