Sur ce site, nous avons maintenant publié plusieurs articles qui traitent de ce merveilleux logiciel qu'est Power Query. Rappelons donc ce que c'est : Power Query est un outil d'extraction, de transformation et de chargement (ETL), intégré notamment à Excel et Power BI, deux logiciels de traitement de données de Microsoft.
En des termes plus clairs, extraction veut dire qu'il s'agit d'un logiciel capable d'aller chercher des données depuis une source externe (un tableau, une page web, un autre classeur Excel…), transformation veut dire que l'outil peut ensuite retravailler les données pour leur conférer une structure optimisée pour Excel, ses formules et ses outils, et enfin chargement signifie insérer les données depuis Power Query vers Excel.
Bien entendu, comme pour tout outil, il y a toujours des bonnes manières de procéder, pour atteindre deux objectifs : que l'outil effectue bien le travail que nous attendons de lui et qu'il le fasse en moins de temps possible. En effet, si nous demandons à quelqu'un d'effectuer un travail en 5 étapes, il mettra probablement moins de temps à le faire que si nous lui demandons de faire le même travail, cette fois en détaillant 15 étapes.
1. Toujours réfléchir en termes de types de données pour les transformations
Dans Power Query, les types de données sont probablement la partie la plus importante, et ce pour au moins trois raisons : tout d'abord, une bonne partie des outils disponibles ne fonctionnent que sur certains types de données et pas d'autres. Par exemple, Power Query ne reconnaîtra pas un nombre en tant que tel si le type de données de la colonne où il se trouve est texte. Et donc tous les outils de calcul de nombre sont grisés sur cette colonne, donc inutilisables.
C'est également la plus grande source d'erreur et de manipulations qui ne fonctionnent pas. La deuxième raison est que Microsoft a disposé ses outils du ruban selon les types de données, et il est donc plus facile de retrouver l'outil adéquat quand on part du type de données.
Quand je fais découvrir les principales transformations de Power Query à mes apprenants, je le présente quasiment toujours de la manière suivante : « Tout d'abord, posez-vous la question : voulez-vous transformer quelque chose d'existant ou bien voulez-vous ajouter une colonne, ou bien aucun des deux ? Ensuite, vient la question du type de données : la transformation cible-t-elle un type de données en particulier ? Allez voir directement dans la rubrique correspondante à ce type de données. »
La dernière raison concerne plus les manipulations avancées : le langage sur lequel Power Query repose est le M. En M, les fonctions existantes sont quasiment exclusivement sous la structure suivante : Type_de_données.Action (par exemple : Number.Round : le type de données est un nombre, et l'action est un arrondi).
D'ailleurs, chaque argument de toutes ces fonctions demande un type de données précis, qui, s'il n'est pas respecté, entraîne une erreur sur toute la formule, invalidant la manipulation recherchée.
2. Minimiser le nombre d'étapes
Moins il y a d'étapes, plus la requête pourra récupérer et transformer les données rapidement. Cela fait également moins de corrections à effectuer sur les erreurs qui commencent à une certaine étape puis qui ruissellent sur toutes les étapes suivantes. De plus, d'un point de vue plus technique, moins le logiciel effectue de calculs, plus rapide il est pour retravailler les données.
Voici quelques petites astuces pour minimiser le nombre d'étapes à effectuer pour Power Query :
- Vérifier qu'il n'y a qu'une seule étape de déplacement de colonnes, à la fin des transformations,
- En cas de mauvaise manipulation, corriger l'étape plutôt qu'en rajouter une autre pour rectifier celle d'avant,
- En cas de besoin de réutiliser une requête dans un autre contexte, utiliser la référence plutôt que la duplication,
- À l'ajout d'une colonne conditionnelle ou personnalisée, nommer la colonne directement plutôt que la renommer plus tard,
- Parfois, une seule colonne personnalisée peut remplacer plusieurs étapes, mais cet outil demande au moins des notions de M.
- Dans les fusions de requêtes, ne développer que les colonnes pertinentes, plutôt que de tout développer puis supprimer les colonnes superflues.
3. Commencer par supprimer les colonnes inutiles
Dans les extractions de données, il y a souvent des colonnes qui ne sont pas pertinentes à l'analyse que nous souhaitons appliquer. Ce sont donc des colonnes inutiles. Ces colonnes inutiles vont donc ralentir le traitement (ce qui va à l'encontre du point précédent, dommage que nous ayons optimisé les étapes !), mais aussi nous perturber dans nos manipulations. Plus facile d'aborder un tableau avec uniquement les données nécessaires que de devoir jongler avec les données ET toute la pollution qu'il y a autour.
4. Bien réfléchir avant de commencer à filtrer
Contrairement à Excel, sur Power Query, un filtre appliqué aura un impact sur toutes les étapes suivantes et sur le tableau final. Donc, avant de filtrer une colonne, vérifier que dans les lignes à supprimer il n'y ait pas des informations importantes dans d'autres colonnes. Typiquement, l'utilisation de cet outil arrive vers la fin du traitement et non pas au début, quand les données sont bien claires et en place.
5. Une colonne = un indicateur = 1 type de données
Nous en revenons au sujet ô combien central dans Power Query : les types de données. Dans une structure idéale à utiliser sur Excel, chaque indicateur a une colonne dédiée, ce qui facilite largement tous les calculs, que ce soit via formule ou via tableau croisé dynamique. Cela permet aussi d'effectuer des transformations de colonnes sans avoir à gérer des erreurs sur la moitié des lignes. Réellement, Excel est fait pour fonctionner en colonnes, pas en ligne ni en double entrée, donc facilitons-nous le travail en gardant cette structure dans nos requêtes !
6. Renommer les étapes
Pour ceux qui ont déjà utilisé Power Query, vous avez (très très) probablement remarqué qu'à chaque manipulation effectuée, une nouvelle étape s'est ajoutée, dans la rubrique « ÉTAPES APPLIQUÉES » à droite de la fenêtre de Power Query. Vous avez probablement aussi remarqué que ces étapes portent toujours des noms génériques de la manipulation effectuée (exemples : « Type modifié », « Personnalisée ajoutée »). Difficile alors de reprendre les étapes et regarder ce qui a été effectué réellement dans ce cas, ce qui rend difficile de déceler un raisonnement général, ce qui rend difficile la maintenance de la requête, si besoin.
Il suffit donc de renommer les étapes de manière plus explicite en faisant un simple clic droit dessus, puis renommer, et ainsi elles seront lisibles et le raisonnement appliqué sera compréhensible, sans aller dans l'éditeur avancé ou la barre de formules pour déchiffrer.
En bonus, il est possible d'ajouter des commentaires pour aller encore plus loin dans l'explicitation des étapes. Pour cela, il suffit de faire un clic droit sur les étapes, puis écrire dans les propriétés.
7. Renommer les requêtes, et les classer
Ceci est particulièrement utile quand il s'agit de jongler avec plusieurs sources différentes, et encore plus quand les sources sont interconnectées. Toujours dans l'esprit de facilité d'utilisation et rapidité de la compréhension, si les requêtes ne sont pas nommées correctement, on se retrouve à aller dedans pour voir le tableau qui y est associé, ce qui va faire perdre énormément de temps et de ressources cognitives. De plus, surtout dans le cas des requêtes qui importent énormément de lignes, ces tableaux ne s'afficheront pas instantanément, entraînant encore plus de perte de temps. Donc, pour accélérer tout ça, autant renommer les requêtes pour voir directement ce qu'elles concernent, sans devoir cliquer une seule fois.
Pour aller encore plus loin, il est possible de classer les requêtes dans des dossiers, pour pouvoir les reconnaître, ou reconnaître les utilisations encore plus facilement.
En somme ?
Il y a bien entendu encore énormément d'astuces à connaître et dont il faut abuser, mais celles-ci sont à mon sens les plus importantes à garder en permanence en tête quand on utilise Power Query. Une fois ces astuces retenues et appliquées dans toutes les requêtes que vous produisez, vous remarquerez que :
- Vos requêtes tournent plus vite,
- Vous arrivez à corriger les erreurs beaucoup plus facilement,
- Vous trouvez les outils, ou utilisez les formules en M beaucoup plus rapidement,
- Vos analyses se font plus naturellement.
Il n'y a donc que des avantages !
