Les paramètres dans Power Query et dans les rapports (2)

par 2 Nov 2019Power Query0 commentaires

Les paramètres Filtre et Tri et les listes de valeurs

Les paramètres-filtres

Il faut le rappeler ici : l’effet d’un paramètre se manifeste au moment de l’envoi de la requête à la source de données.

Un paramètre-filtre de requête est donc très différent d’un filtre de rapport (slicer), puisque le paramètre va réduire le volume de données.

C’est par conséquent particulièrement utile lorsqu’il s’agit de maîtriser la masse de données ramenée au moment de l’actualisation du rapport. Associé à un modèle de document (PBIT), le paramètre-filtre est un outil particulièrement efficace pour encadrer la requête envoyée par un utilisateur.

Dans une moindre mesure, c’est un outil qui facilite la modification du périmètre de la requête (basculer rapidement d’un périmètre à un autre, sans avoir besoin de passer par Power Query).

Le point de départ, à nouveau, c’est créer une première requête sur la source : dans cet exemple, un fichier Excel de relevés de la qualité de l’air, fourni par l’OMS. Le fichier propose des relevés des niveaux de particules PM10 par Région, Ville et Année, entre autres. Il fait 12000 lignes, et je veux m’assurer qu’un nombre restreint de lignes sera extrait.

Je vais créer un paramètre-filtre pour l’année, un autre pour la région. La subtilité ici, c’est que je souhaite que ces paramètres s’appuient sur une liste de valeurs générée automatiquement à partir des données du fichier.

Rappelez-vous : lors de la création d’un paramètre, un choix s’offre, de permettre toutes les valeurs, de les limiter à une liste saisie manuellement, ou d’utiliser une requête. C’est cette troisième option qui va nous intéresser.

Regardons-en dans un premier temps la démarche générale :

  1. Il s’agit d’abord de créer une nouvelle requête basée sur la même source (utilisez pour ça le raccourci Sources récentes)
  2. Puis ne garder qu’une colonne (Supprimer les autres colonnes)
  3. Supprimer les doublons
  4. En enfin, convertir cette requête en liste

J’effectue aussi cette opération pour les années, pour me retrouver avec deux listes.

Passons maintenant aux paramètres. Leur création ne pose aucune difficulté, puisque les listes sont disponibles avec l’option … Requête :

A ce stade, j’ai donc 5 entrées dans la zone requête de Power Query :

  • La liste Années
  • Le paramètre Année
  • La liste Régions
  • Le paramètre Région
  • La requête utile, celle dont je me servirai dans le rapport

C’est sur cette dernière que je vais maintenant appliquer un filtre basé sur chacun des paramètres.

Attention : à ce jour, il n’est pas possible de créer simplement des « listes en cascade », c’est-à-dire des listes dont les valeurs dépendent d’un autre paramètre (ex., une liste de villes générée en fonction du pays choisi préalablement). Par ailleurs, il n’est pas possible de choisir plusieurs valeurs lors de l’appel du paramètre. Ces deux points sont prévus dans une version prochaine de Power BI.

Lors de l’application d’un filtre textuel « Egal à » sur la colonne Région, je vais pouvoir invoquer le paramètre et le choisir dans la liste déroulante (il est d’ailleurs possible de créer le paramètre à la volée) :

Cette opération est répétée pour les années.

Notez le script M résultant :

 = Table.SelectRows(#"Lignes filtrées", each [Year] = Année)
et
= Table.SelectRows(#"Colonnes supprimées", each [Region] = Région)

 Remarque : avant de passer au rapport lui-même, notez qu’il est inutile de charger les listes Année et Région !

Reste à appliquer et fermer Power Query. Le résultat de la requête tient compte des deux valeurs actuelles définies à la création des paramètres.

Pour exécuter la requête sur un autre périmètre (c’est là que la démarche pêche un peu, mais nous verrons comment améliorer ce point avec les modèles de document), il faut utiliser la fonction Modifier les requêtes > Modifier les paramètres, et choisir dans la liste déroulante.

Attention à bien penser à appliquer les modifications (à relancer la requête) :

Si vous souhaitez afficher sur le rapport les choix de région et d’année, la formule suivante suffit :

                AnnéeRégion = MAX(PM_10[Year]) & " / " & MAX(PM_10[Region])

 

Les paramètres modificateurs du script de la requête

Les paramètres peuvent également servir à rendre dynamique des portions du script M de la requête.

Dans le scénario que je vous propose, je souhaite extraire le top X des villes en termes de moyenne annuelle de particules PM10 (colonne Annual mean du tableau) pour une région et une année données.

Ici, top X signifie que je laisse à l’utilisateur le soin d’extraire 5, 10 ou 25 villes (je pourrais bien entendu laisser un choix totalement ouvert, voire facultatif).

Sur la base du fichier précédent, je vais donc créer un nouveau paramètre Top X :

Je vais ensuite, en m’appuyant sur l’UI, trier les lignes de la table par ordre décroissant de moyenne annuelle. Enfin, toujours à l’aide de l’UI (fonction Conserver les lignes > Conserver les premières lignes), je vais appeler et choisir dans la liste déroulante le paramètre Top X :

Notez le code M généré :

      = Table.FirstN(#"Lignes triées",#"Top X")

(Ici, l’appel du paramètre est encadré de doubles guillemets et précédé d’un dièse : c’est parce son nom contient un espace).

***

En définitive, si j’actualise le rapport, voici la fenêtre des paramètres et le résultat :

Ceci est un exemple parmi beaucoup d’autres d’utilisation des paramètres pour modifier le script.

Dans le prochain article, j’évoquerai les modèles de document PBIT et leur rapport avec les paramètres et les fonctions dans Power Query