Power Query, un allié de choix pour Excel

15/01/2020

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 :

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, retraiter et restituer des données : comment Power Query peut faire d’Excel une alternative sérieuse à Power BI ?

1. 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 :

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

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

NB : il est possible de ne pas charger ses requêtes dans Excel et de ne garder qu’ine simple connexion dans Power Query (Only Create Connection).

 

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 :

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.

3. 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 :

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

 

Voici la présentation du Meetup Power BI :

Posez vos questions en commentaire !