Power Query : 3 trucs faciles pour optimiser et épurer vos requêtes

Publié le 18 avril 2024
par Audrée Pellerin M.Sc.
Optimisation de requêtes Power Query

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.

Power Query Optimisation

 

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

Optimisation de requêtes

 

Le résultat obtenu ressemble à ceci :

Optimisation de requêtes

 

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.

Optimisation de requêtes Power Query

 

Le résultat obtenu est le suivant :

Optimisation de requêtes Power Query

 

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 :

Optimisation de requêtes Power Query

 

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.

Optimisation de requêtes Power Query

 

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)

 

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

Power Query Optimisation de requêtes

 

B) Dans le menu « Transformer », cliquez sur « Extraire » puis « Texte avant le délimiteur »

Power Query Optimisation

 

C) Indiquez le délimiteur « @ » puis appuyez sur Ok

Power Query Optimisation

 

Remarquez que la transformation a été appliquée aux 2 colonnes dans la même étape de transformation.

 

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

Power Query Optimisation

 

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 :

Power Query Optimisation

 

Toutefois, la requête optimisée comprend seulement 4 étapes alors que la requête « non-optimisée » en contient 11.

Power Query Optimisation

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 :

Voici quelques commentaires d’apprenants ayant suivi la formation - Excel Introduction à Power Query et au langage M

CFO-Masque_Formations-en-ligne_FBLa 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é.

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Retour en haut