Lors de la création d’une requête Power Query, il est primordial d’optimiser la performance. Surtout lorsque la volumétrie des données transformées est importante. Cette optimisation de la performance vous permettra, entre autres, d’éviter des erreurs de type Timeout et de préserver la santé mentale des utilisateurs de vos fichiers Excel 😉
Les trucs listés dans cet article serviront à la fois à améliorer la performance mais aussi à épurer votre requête de façon à en faciliter la compréhension.
Pour démontrer ces concepts, j’utiliserai les données suivantes qui listent le nombre de rendez-vous planifiés pour chaque journée du mois d’avril.
-
Création de colonne
Lors de la création de nouvelles colonnes, assurez vous de tirer profit au maximum de ce que vous pouvez faire dans une seule étape. Je vais donc m’assurer de nommer ma nouvelle colonne ET de lui attribuer le bon type de données à l’intérieur de la même étape.
Par exemple, si je souhaite créer une colonne conditionnelle qui renvoie le type de journée (petite, moyenne, grosse) dépendant de la quantité de rendez-vous prévus comme ceci :
Le résultat obtenu ressemble à ceci :
Pour terminer les étapes de création de notre nouvelle colonne, on devra ensuite renommer la colonne et appliquer un type de données « Texte » à la colonne, créant 2 étapes de transformation supplémentaires.
Le résultat obtenu est le suivant :
Essayons maintenant d’optimiser cette création de colonne
Tout d’abord, vous avez probablement remarqué qu’il était possible de nommer notre colonne directement à l’intérieur de la boîte de dialogue de création de colonne conditionnelle :
Mais saviez vous qu’il est aussi possible d’appliquer le type de donnée de la colonne directement dans cette étape? Pour ce faire, on doit modifier légèrement le code M généré automatiquement par Power Query.
Le code généré par ma création de colonne est le suivant :
= Table.AddColumn(Source, "Type de journée", each if [Quantité] < 5 then "Petite Journée" else if [Quantité] < 10 then "Moyenne Journée" else "Grosse Journée")
Pour appliquer directement le type de données à la colonne, il est possible d’ajouter un 4e paramètre à la fonction Table.AddColumn.
Dans ce cas-ci, je souhaite appliquer un type de données « Texte » à ma colonne. J’utiliserai donc le paramètre columnType pour passer la valeur « type text ».
Pour connaître les valeurs acceptées pour le paramètre columnType, consultez la liste suivante : https://learn.microsoft.com/fr-ca/powerquery-m/m-spec-types
Le code M final pour cette étape sera donc :
= Table.AddColumn(Source, "Type de journée", each if [Quantité] < 5 then "Petite Journée" else if [Quantité] < 10 then "Moyenne Journée" else "Grosse Journée", type text)
-
Modification de colonne
Si vous avez des modifications à appliquer à plusieurs colonnes, rassemblez ces transformations dans une seule étape ! Par exemple, je souhaite extraire le prénom de l’employé et du responsable plutôt que de conserver l’adresse courriel complète. Savez vous qu’il est possible de réaliser cette transformation sur les 2 colonnes en même temps ?
Voici comme y arriver :
A) Sélectionnez toutes les colonnes sur lesquelles appliquer cette transformation
B) Dans le menu « Transformer », cliquez sur « Extraire » puis « Texte avant le délimiteur »
C) Indiquez le délimiteur « @ » puis appuyez sur Ok
Remarquez que la transformation a été appliquée aux 2 colonnes dans la même étape de transformation.
-
Nettoyez vos requêtes
Au moment de la création de vos requêtes, c’est normal que certaines étapes superflues s’ajoutent à votre liste d’étapes. Vous êtes en train de développer, de tester, de découvrir l’interface et les données avec lesquelles vous travaillez.
Par contre, lorsque tout est terminé et que vous maîtrisez mieux le processus vous permettant de transformer vos données brutes en belles tables de données, revenez sur chacune de vos étapes et demandez vous si elle était nécessaire.
Voici un exemple que je vois régulièrement en entreprise.
Les étapes encadrées en rouge ici sont superflues! L’impact de ces transformations est annulé par des transformations subséquentes donc peuvent être éliminées.
L’étape « Colonnes permutées » permet de changer l’ordre des colonnes dans la requête. Le code M qui est généré est le suivant :
À l’étape « Colonnes permutées1 », on change encore une fois l’ordre des colonnes avec le code suivant :
Puis finalement, à l’étape « Colonnes permutées2 », on détermine une bonne fois pour toute, l’ordre souhaité des colonnes :
Il est très fréquent de modifier l’ordre des colonnes pendant la création de la requête pour avoir une meilleure visibilité sur les données avec lesquelles on travaille. Toutefois, ces étapes sont souvent superflues et sans conséquence sur le résultat final de la requête. Il est donc important de les supprimer pour épurer et optimiser nos requêtes !
C’est la même chose lorsque plusieurs tris sont effectués sur les données à travers la requête.
Conclusion
Les résultats obtenus sont les mêmes que l’on ait utilisé une requête « optimisée » ou non :
Toutefois, la requête optimisée comprend seulement 4 étapes alors que la requête « non-optimisée » en contient 11.
Et il s’agissait ici d’une transformation de données très simpliste. Vous comprenez donc qu’avec une requête plus importante, les étapes de transformation seraient multipliées et ça deviendraient rapidement très difficile de s’y retrouver.
Fichier d’accompagnement VIP à télécharger
Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.
Formation complémentaire
Pour découvrir comment optimiser et automatiser vos requêtes de transformation de données, et économiser un temps fou, suivez la formation Excel – Introduction à Power Query et au langage M.
Voici quelques commentaires d’apprenants ayant suivi cette formation :
La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.
