VBA et Power Query permettent d’automatiser les tâches de transformation de données sur Excel et Power BI. Découvrez tout ce que vous devez savoir sur la création de macros !
Afin d’importer et de transformer les données sur Excel et Power BI, on utilise couramment le moteur Power Query. Cet outil permet de manipuler les données à partir de sources externes.
Toutefois, de nombreux utilisateurs ignorent qu’il est possible d’utiliser le langage de programmation VBA (Visual Basic for Applications) pour créer des macros d’automatisation de transformation de données.
C’est un excellent moyen de gagner en efficacité sur les tâches de manipulation de données. Découvrez à présent tout ce que vous devez savoir sur ce sujet !
Qu’est-ce que VBA ?
Le langage de programmation VBA (Visual Basic for Applications) est directement intégré aux outils de la suite Microsoft 365.
Il permet de créer facilement des macros, afin d’automatiser les tâches les plus répétitives dans les logiciels comme Excel, Word, Access, PowerPoint et Outlook.
Qu’est-ce que Power Query ?
Intégré à Excel, Power BI et différents outils de l’écosystème Microsoft Power Platform, Power Query permet l’extraction, la transformation et le chargement (ETL) de données.
On l’utilise notamment pour charger, nettoyer et transformer les données en provenance de différentes sources. Il peut s’agir de fichiers Excel, CSV, de bases de données ou encore de services cloud et de pages web.
Les opérations de transformation de données incluent le filtrage, le tri, le regroupement ou encore le fractionnement et le regroupement de colonnes. Il est également possible d’ajouter des colonnes personnalisées à l’aide des formules Power Query.
Pour créer des relations entre les tableaux, on peut aussi les lier à l’aide de clés étrangères. L’outil permet aussi de créer des requêtes dynamiques pour mettre à jour les données à mesure qu’elles sont ajoutées ou modifiées dans la source externe.
Le point fort de Power Query est son interface intuitive et simple d’utilisation pour l’importation et la transformation de données. Toutefois, répéter les mêmes tâches de transformation inlassablement pour différentes sources peut vite devenir fastidieux. Le langage VBA apporte une alternative.
À quoi sert VBA avec Power Query ?
En utilisant VBA conjointement à Power Query, il est possible d’automatiser les tâches de transformation de données dans le but de gagner en efficacité.
La première étape est de créer une requête Power Query, via l’interface utilisateur d’Excel ou l’éditeur de Power BI. Dès que la requête est créée, elle peut être enregistrée sous forme de macro d’automatisation qu’il est possible de modifier librement. Voici plusieurs exemples de cas d’usage.
Tout d’abord, il est possible d’automatiser le processus d’importation de données à partir de différentes sources avec des macros VBA. On peut ensuite transformer les données avec Power Query pour les rendre plus exploitables, et créer des visualisations avec Power BI.
Pour les tâches de nettoyage de données, VBA permet par exemple d’automatiser la suppression des lignes en double et les colonnes inutiles, de remplacer les valeurs manquantes, ou de convertir les données en différents formats.
Par ailleurs, VBA peut être utilisé pour combiner plusieurs sources de données à l’aide de Power Query. Il peut notamment s’agir de plusieurs feuilles Excel à combiner en une seule requête.
De la même manière, plusieurs données provenant de différentes sources externes peuvent être combinées. Ceci permet par exemple de combiner un fichier CSV avec une base de données.
Rien n’empêche de créer une macro VBA pour créer des connexions Power Query pour plusieurs tableaux Excel. C’est un gain de temps par rapport à la création manuelle d’une connexion pour chaque tableau à combiner.
Un autre exemple de cas d’usage est l’utilisation d’une macro VBA pour créer un bouton de rafraîchissement, permettant à l’utilisateur d’automatiser les requêtes directement sur Excel ou Power BI.
En règle générale, toutes les tâches de transformation répétitives peuvent être automatisées avec VBA. Son utilisation est donc très pertinente pour des processus à effectuer avec Power BI sur chaque nouvelle source de données. Cependant, cette synergie présente des limites.
Quelles sont les limites ?
Les macros VBA sont très utiles, mais ne peuvent dépasser un certain seuil de complexité. Une tâche de transformation de données trop ardue requiert une macro compliquée qu’il sera difficile de maintenir correctement.
De même, Power Query est un puissant outil de manipulation de données, mais ne prend pas en charge certaines sources de données. Il est également restreint en termes de transformation de données.
Sur des sources de données comportant des relations complexes entre les tableaux, l’automatisation des tâches de transformation peut aussi être difficile.
En guise d’alternative, il est possible d’utiliser des outils de manipulation de données plus avancés comme les langages Python, R ou SQL pour la création de scripts d’automatisation.
Conclusion
Le langage VBA est très utile pour automatiser les tâches de transformation de données sur Power Query pour Excel et Power BI. C’est un précieux atout pour les processus les plus répétitifs, bien qu’il présente ses limites pour les manipulations complexes.
Afin d’apprendre à manier Power Query, le langage VBA et les autres outils avancés d’Excel ou Power BI, vous pouvez choisir DataScientest.
Notre formation intensive à Power BI se déroule en 38 heures réparties sur 5 jours. Les 3 premiers jours sont dédiés à l’initiation pour les débutants, et les 2 jours suivants permettent de renforcer son expertise et s’adresse aux initiés.
Il est possible d’effectuer la totalité de parcours, ou de choisir l’une des deux parties en fonction de vos besoins et de votre niveau de compétences.
À la fin du cursus, vous maîtriserez pleinement Power BI et serez en mesure de l’utiliser pour collecter, transformer, analyser des données et créer des tableaux de bord interactifs. Les langages DAX, M, VBA et le moteur Power Query n’auront plus de secrets pour vous.
Tous nos professeurs sont certifiés Microsoft Trainer, et notre organisme a le statut de Microsoft Learning Partner. Ainsi, nous vous accompagnons jusqu’à l’obtention de la certification Microsoft PL-300 pour devenir Power BI Data Analyst Associate !
Le programme se complète intégralement à distance via le web, et nous sommes éligibles au Compte Personnel de Formation pour le financement. Découvrez DataScientest !
Vous savez tout sur l’alchimie entre VBA et Power Query. Pour plus d’informations sur le même sujet, découvrez notre dossier complet sur Power BI et notre dossier sur Power Query.
