Power Query est la réponse à l’optimisation de notre charge de travail pour nos reportings Excel. Une bonne partie du travail de reporting réside dans la récupération et la préparation des données. Ce temps consacré en amont du véritable travail d’analyse est du temps sans valeur ajoutée. De plus, la plupart des jeux de données sur lesquels nous travaillons, sont normalisés. Nous oeuvrons à partir des mêmes exports ou des mêmes bases de données. Power Query nous permet de récupérer ces données et de les transformer afin que nous puissions les travailler et les analyser. Dans cet article je vous explique ce qu’est Power Query, ses avantages et comment débuter dans son utilisation. C’est parti !
Power Query, c’est quoi ?
Selon Microsoft, “Power Query est un moteur de transformation de données et de préparation des données.”. Il s’agit d’un outil de BI (business intelligence) permettant d’importer des données, de les nettoyer, de les transformer et de les restructurer dans le format que nous souhaitons. Power Query est ce qui peut s’apparenter à un ETL (Extract, Transform and Load) mais avec des visées grand public.
Une fois que nous avons correctement défini les processus par lesquels les données doivent être transformées, il suffira par la suite d’actualiser les sources de données pour que nos données corrigées soient mises à jour.
Parmi les transformations possibles : suppression de colonnes, ajout de colonnes avec formules, transformation et formatage des données, suppression des doublons, fusion des sources,…
Pourquoi utiliser Power Query ?
Les avantages de l’utilisation de Power Query sont nombreux. Il est disponible dans quasiment toutes les versions d’Excel (2010 et 2013 en add-in puis en natif à partir d’Excel 2016). J’ai identifié plusieurs avantages majeurs à utiliser Power Query dans notre travail de reporting sur Excel.
Facilité d’utilisation
La plupart des outils dans Excel peuvent être complexes au premier abord. Il faut connaitre les formules pour pouvoir les utiliser dans les cellules. La programmation VBA et les macros, même avec l’aide de l’enregistreur automatique, demande d’avoir quelques notions de base. Power Query, bien au contraire, ne requiert aucune connaissance et vous n’avez pas besoin d’apprendre de formule ou de code. La prise en main est aisée grâce à son interface facile d’utilisation. Vous pourrez par la suite vouloir comprendre le langage M (langage utilisé pour définir les transformations) mais ce n’est pas obligatoire pour arriver à vos fins. L’interface utilisateur est parfaitement compréhensible et on assimile facilement les différentes fonctions.
Puissance de calcul
Lorsque nous travaillons dans Excel, il arrive parfois, ou même régulièrement, de se retrouver confronter à un fichier trop lourd. C’est alors que votre ordinateur connait des ralentissements. C’est très frustrant lorsque, d’autant plus, nous avons un quotidien surchargé et des supérieurs impatients de recevoir le reporting Excel. Heureusement, Power Query vient résoudre en grande partie ce problème. Nous pouvons importer dans Power Query des millions de lignes dans un modèle de données. Même en effectuant des modifications importantes, les ressources requises pour les effectuer sont limitées. La puissance de calcul est impressionnante. Passer par Power Query garantit d’avoir une fichier sans ralentissement comparativement à des imports dans des onglets avec des retraitements dans ces mêmes onglets. C’est certainement une solution à envisager pour vous qui souffrez d’un classeur trop lourd.
Multi sources
Nos jeux de données peuvent provenir de sources très différentes. Majoritairement, ils viendront d’autres classeurs précédemment exportés de votre logiciel préféré. Power Query peut se connecter à des sources différentes et nombreuses. Parmi celles-ci, on trouve : des documents Excel, CSV, JSON, XML ou PDF, des bases de données SQL ou Access,… On peut aussi récupérer des données directement à partir d’internet ou dans un Sharepoint. La seule limite que j’ai pu rencontrer était la récupération de données dans une base de données WinDev. La faute à WinDev…
Maintenant que j’ai piqué votre intérêt pour Power Query, voyons ensemble comment démarrer sur ce module.
Comment utiliser Power Query ?
Power Query est nativement présent dans Excel depuis Excel 2016. Pour Excel 2010 et Excel 2013, un add-in est téléchargeable. Power Query se cache derrière l’onglet Données dans votre ruban supérieur. Les deux encarts sur la gauche sont des fonctionnalités de Power Query.
La section Récupérer et transformer des données vous permettra de charger vos sources de données et d’y apporter des modifications avant chargement. La section Requêtes et connexions sert à rafraichir vos sources de données afin de récupérer des jeux de données actualisées. Fini les retraitements récurrents. Votre modèle de transformation s’applique à vos données mises à jour.
Pour présenter le potentiel de Power Query, je vous propose un exemple. Vous travaillez pour Netflix et vous devez effectuer un reporting sur les acteurs présents dans les séries et films diffusés. Pour vous aider, on vous communique un export au format CSV. Le format CSV est un fichier dont les enregistrements sont séparés par des retours à la ligne et les colonnes par des virgules. On vous a prévenu à l’avance que le nombre de lignes présentes dans le fichier allait connaître une croissance exponentielle. Power Query est donc la solution pour la maintenabilité de votre reporting Excel.
Je vous faire plusieurs opérations sur mon fichier afin que vous puissiez prendre conscience de la puissance de Power Query. Parmi ces opérations je vais :
- Supprimer des colonnes
- Transformer des colonnes
- Ajouter des colonnes
- Supprimer des doublons
Commençons par l’importation des données. En effet, je souhaite lier mon fichier d’export CSV à mon futur reporting Excel grâce à Power Query.
Importation des données
Pour importer vos données dans Power Query, rendez-vous dans l’onglet Données de votre ruban. Il vous suffit alors de cliquer sur Obtenir des données et de sélectionner A partir d’un fichier et enfin A partir d’un fichier texte/CSV.
Par la suite, venez récupérer votre fichier grâce à la boite de dialogue ouverte. Comme expliqué précédemment, on voit la puissance de Power Query pour vos reportings Excel grâce aux nombreuses sources pouvant être liées à votre fichier. Le tout pouvant être combiné.
Une fois votre fichier sélectionné, la fenêtre suivante apparait. Vous voyez un extrait de votre fichier. Dans notre cas, on constate que Power Query a reconnu que mon fichier était un CSV avec des virgules en tant que délimiteur. Je pourrais alors cliquer sur Charger mais ce serait passer à côté de la puissance de Power Query pour vos reportings Excel. Cliquons sur Transformer les données.
Transformation des données
Notre futur reporting Excel se concentre sur les acteurs les plus présents dans les films et séries Netflix. Nous allons pouvoir supprimer toutes les colonnes inutiles, en modifier d’autres et en ajouter une.
La première opération effectuée est la suppression des colonnes inutiles. Il y a plusieurs méthodes et celle que je vous propose est de simplement cliquer sur l’entête de la colonne. Vous verrez alors par vous même le champ des possibles qui s’ouvre à vous en terme de transformation des données.
Notez également l’affichage des étapes appliquées sur la droite. Il s’agit des opérations de transformation qui sont appliquées sur notre jeu de données ainsi que l’ordre de ces modifications.
Pour la seconde opération, nous avons été prévenu qu’il pouvait y avoir des titres en doublon dans notre fichier. Je supprime les doublons en faisant un clic droit sur l’entête de la colonne puis en sélectionnant Supprimer les doublons. C’est si simple !
A présent, je souhaite me concentrer sur les acteurs des films et séries. J’aurai besoin par la suite de faire un reporting dans mon classeur Excel sur le nombre de participations de ces acteurs. Dans mon fichier, j’ai la liste des acteurs dans une cellule et ils sont séparés par des virgules. Il me faut démultiplier mon fichier afin d’avoir un acteur par ligne. Imaginez-vous devoir le faire sans Power Query. Maintenant faisons le avec Power Query.
Je clique sur l’entête de la colonne. Je sélectionne Fractionner ma colonne puis Par délimiteur. Dans l’écran suivant je précise que mon délimiteur est une virgule et qu’il faut traiter chaque virgule. Je précise aussi que je veux éclater mes valeurs en lignes et non pas en colonnes. Le tour est joué ! Le gain de temps est impressionnant.
Dernière étape, je souhaite ajouter une colonne avec uniquement le mois et l’année de la sortie du film à partir d’une colonne date_added qui est sous le format JJ/MM/YYYY. Je clique sur Colonne personnalisée et j’inscrit une formule permettant de changer le format. Ma colonne est prête.
Après ces transformations, mes données sont prêtes à être importées dans Excel. Outre la création de colonnes qui peut potentiellement demander quelques minutes de questionnement à notre ami Google pour trouver la bonne formule, le reste a été très intuitif et accessible à tou(te)s. Chargeons nos données dans notre fichier.
Chargement des données dans Excel
Pour charger vos données dans Excel, rien de plus simple, cliquez sur le bouton Fermer et charger.
Power Query va importer vos données sous forme de tableau dans un nouvel onglet de votre reporting Excel. Dans notre cas, le fichier initial comprenait plus de 2000 lignes. Après avoir demandé à séparer les acteurs sur des lignes différentes, le tableau comprenait 65000 lignes. Le temps de chargement a été immédiat.
Actualiser les données de Power Query
La question qui peut rester en suspens à présent est de savoir comment actualiser nos données Power Query afin de mettre à jour notre reporting Excel. Pour ça, faites un clic droit sur le tableau généré par Power Query et cliquez sur Actualiser. Si votre fichier a été déplacé, il faudra lier le nouveau fichier. Toutes les modifications effectuées sur le fichier initial ont été mémorisées. Vous n’aurez donc plus rien à faire.
J’espère vous avoir convaincu que la prise en main de Power Query pour vos reportings Excel est aisée. Pensez-y pour vos prochains travaux dès lors que vous passerez de longues heures sur des retraitements redondants.
Conclusion
Power Query peut sembler obscur à un néophyte lors d’une première approche. Il s’agit d’un outil extrêmement puissant caché derrière l’onglet Données de votre ruban Excel. Comme vous avez pu le constater la prise en main s’effectue de manière intuitive. L’expérience utilisateur est très agréable et la prise en main se fait en douceur. Vous pouvez charger et transformer vos données sans expérience ni compétence préalable. Commencez dès maintenant à vous servir de Power Query pour vos reportings Excel et dites moi en commentaire votre ressenti. Je me ferai un plaisir de vous lire (et vous répondre😁).
