Pourquoi rendre vos filtres dynamiques dans Power Query ? Lors de la création de fichiers Excel, on mise généralement sur le dynamisme et la flexibilité des hypothèses de base. Par exemple, si on modélise les dépenses de notre entreprise pour les 3 prochaines années, certaines dépenses comme les taux d’intérêt et le prix de l’essence devront être dynamiques. En effet, ces hypothèses vont très probablement varier et on ne veut pas avoir à refaire notre modèle financier pour tenir compte de ces modifications.
Un autre exemple de dynamisme qui est généralement apprécié dans Excel est au niveau des dates. Si on cherche à générer un rapport pour une année spécifique, on va se créer un champ « Année » dans notre fichier et il suffira de faire varier cette cellule pour que le rapport entier soit actualisé avec la bonne année.
Maintenant qu’on a compris et appliqué le concept dans Excel, comment on fait pour reproduire ça dans Power Query ?
Il est possible d’utiliser les Paramètres dans Power Query pour insérer une variable dans nos requêtes. Les paramètres permettent de dynamiser les requêtes mais jusqu’à un certain point. Dans Excel, on doit ouvrir Power Query pour modifier la valeur d’un paramètre. Dans Power BI, les paramètres peuvent être modifiés a l’ouverture d’un fichier .pbit (Templates Power BI) ou par les paramètres du jeu de données dans Power BI Service. Ce n’est donc pas 100% dynamique.
Pour rendre vos filtres dynamiques dans Power Query (ou un autre type d’étape de transformation), voici la méthode que je privilégie :
- Étape 1 : Créer une requête (ou une étape d’une requête) qui retourne la valeur dynamique
- Étape 2 : Utiliser cette valeur à l’intérieur d’un filtre (ou d’une autre étape de transformation)
Étape 1. Créer une requête qui retourne une valeur dynamique
À cette étape, la clé du succès est de construire une requête qui retournera une valeur unique de façon dynamique. Les méthodes pour y arriver sont diverses. En voici quelques unes.
1. a) Source dynamique
Lorsque je prépare un fichier excel pour lequel je veux que les utilisateurs puissent modifier une hypothèse, je crée une cellule qui sert « d’input ». Par exemple, si l’utilisateur veut que les requêtes Power Query s’effectue uniquement sur les données de 2023, il saisira 2023 dans la cellule prévue à cet effet avant d’actualiser les requêtes.
Pour importer une cellule spécifique dans Power Query, vous devez la transformer en tableau ou la nommer. Vous pourrez ensuite importer cette valeur dans Power Query en cliquant sur « À partir d’une plage ou d’une table » dans le menu « Données »
Lorsque la valeur est dans Power Query, on peut effectuer un drill-down sur celle-ci (Clic-droit sur la valeur + drill-down) pour qu’elle devienne utilisable dans d’autres requête.
La valeur pourra maintenant être appelée en référant au nom de la requête (Annee).
1. b) Requête dynamique
Lorsque vous souhaitez que la requête elle-même détermine de façon dynamique le filtre à appliquer, il est possible de mettre en place des étapes de transformation à appliquer. Par exemple, si je veux toujours faire l’analyse de l’année la plus récente disponible dans une base de données, je peux me connecter à la liste de données disponible et organiser ma requête de façon à conserver uniquement la valeur maximale de la colonne « année ». Si la colonne « année » n’existe pas dans vos données, n’hésitez pas à la créer à partir d’une colonne de dates! Par exemple, si vous travaillez avec des fichiers de ventes quotidiens et que vous ne voulez importer que les fichiers de l’année la plus récente dans votre requête, voici comment procéder.
Tout d’abord, importez la liste de fichiers en vous connectant au dossier dans lequel les fichiers se trouvent.
À partir de cette liste de fichier, effectuez les manipulations nécessaires pour obtenir l’année de chaque fichier. Dans mon exemple, j’utilise les noms de fichier qui sont toujours formatés AAAA-MM-JJ.xlsx pour créer une colonne de dates. (Ajouter une colonne / Extraire / Texte avant le délimiteur / Délimiteur = « .xlsx »)
Formater cette nouvelle colonne en Date et extraire l’année de cette colonne (Ajouter une colonne / Date / Année / Année)
Utiliser ensuite la fonction Grouper Par pour obtenir la valeur maximale de la colonne Année.
Vous devriez obtenir une valeur unique correspondant à l’année maximale de votre liste de fichiers. Vous pouvez ensuite « Drill-down » sur cette valeur pour l’utiliser dans d’autres requêtes.
Il s’agit ici de deux exemples que je vois couramment chez mes clients mais vous pouvez laissez aller votre imagination! Le but est d’arriver à une valeur unique sur laquelle ou pourra effectuer un drill-down et ensuite l’utiliser dans le filtrage d’une autre requête.
Vous avez des sources de données volumineuses à rassembler et analyser dans Excel ? Apprenez à automatiser tout le processus d’importation, de transformation et de modélisation des données avec nos formations sur Power Query et Power Pivot dans Excel. |
|---|
Étape 2. Utiliser cette valeur dynamique
Maintenant que vous avez créé votre requête dynamique retournant une valeur unique, vous pouvez l’utiliser pour filtrer vos requêtes. Voici comment faire.
2. a) Requêtes distinctes
Si vous souhaitez filtrer les données d’une requête X à partir du résultat d’une requête Z, voici comment vous y prendre. Dans ce cas-ci, je vais filtrer la requête « Commandes » à partir du résultat de la requête « Annee »
À partir de la colonne Date de la requête « Commande », je crée une colonne d’année (Ajouter une colonne / Date / Année / Année).
J’applique ensuite un filtre sur cette colonne. Temporairement, je vais filtrer sur l’année 2021. J’utilise ce filtre temporaire pour créer le code M qui me sera nécessaire à filtrer la colonne Année. Lorsque le code M sera créé, je vais le modifier légèrement pour faire référence au résultat de la requête « Annee » plutôt qu’à 2021.
Voici le code M qui est généré :
Je modifie donc la valeur « 2021 » pour la remplacer par la valeur de la requête « Annee ». Remarquez que la valeur « Annee » est reconnue comme une variable.
Lorsque je confirme ma modification, les données sont maintenant filtrées sur 2023 (parce que c’était la valeur présente dans la requête « Annee ».
2. b) À l’intérieur même d’une requête :
Revenons à l’exemple de la liste de fichiers de ventes quotidiennes. J’ai appliqué des étapes de transformation à une liste de fichier pour obtenir l’année maximale de la liste. Je pourrais créer une 2e requête pour appliquer un filtre sur l’année des fichiers et appliquer la même logique qu’à l’exemple précédent. Mais je peux aussi réaliser toutes ces étapes dans la même requête! Voici comment.
Tout d’abord, allons voir à quoi ressemble notre requête à ce stade-ci.
Au cours de cette requête, on s’est connecté à une liste de fichiers (étape 1), on a ajouté une colonne d’année (étape 2) et on a groupé ces données et fait un drill-down pour obtenir une valeur unique (étape 3).
On veut maintenant retrouver la requête dans l’état où elle était avant de grouper les données. Pour ce faire, on peut ajouter une étape à la requête qui nous ramène à une étape précise. Dans ce cas-ci, à l’étape « Inserted Year ».
Pour ajouter une étape, on peut cliquer sur le bouton Fx à gauche de la barre de formule.
Par défaut, cette nouvelle étape fera référence au nom de l’étape précédente.
On peut modifier ce bout de code M pour faire référence à l’étape « Inserted Year » pour ramener la requête à l’état où elle se trouvait à cette étape-là.
On peut ensuite filtrer la colonne « Année » temporairement sur « 2021 ».
Et finalement ajuster ce filtre pour faire référence à la valeur « AnneeMax » qui avait été calculée précédemment.
Encore une fois, il s’agit ici d’exemple couramment rencontrés chez mes clients. N’hésitez pas à laisser aller votre imagination pour appliquer les principes des filtres dynamiques dans Power Query à vos besoins d’automatisation !
Visionnez nos tutoriels sur YouTube!
Nous avons préparé ces 2 capsules pour vous permettre de voir le tout en action.
Bonne écoute!
Abonnez-vous à notre chaîne YouTube !
Que ce soit des extraits de formations, des tutoriels rapides couvrant diverses notions (Excel / Power BI) ou des publications dans notre section ‘Communauté’, nous avons de tout.
N’hésitez pas à explorer notre contenu, laissez-nous vos impressions dans les commentaires et proposez des idées de vidéos que vous aimeriez voir !
➤ Abonnez-vous ici
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
Afin d’explorer les principales fonctions et fonctionnalités de Power Query, qui permet d’importer, de transformer et de fusionner des données de diverses sources et de pouvoir les analyser efficacement, 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é.
