Les paramètres dans Power Query et dans les rapports (1)
Le paramètre Source de données, création d’un paramètre
Les paramètres offrent la possibilité de modifier le périmètre de la requête Power Query, de passer par exemple d’une base de données de DEV à une base de PROD, d’un fichier à un autre, ou encore de modifier, de manière simple, le filtre appliqué à une ou plusieurs colonnes (changer d’année, de produit, etc.).
Dans la première partie de ce dossier, j’évoquerai tout ce qui touche aux paramètres de Power Query, et leur création, ainsi que les limites de la solution.
Associé à un modèle de document (template .pbit), ils permettent de maîtriser la manière dont l’utilisateur va interroger la source de données, tout en lui laissant une certaine dose de liberté quant au périmètre de la requête. Et ce dans le but principal de limiter la taille du résultat.
Enfin ils peuvent également servir de base à la création d’une fonction : nous en verrons un exemple facilitant la création de table.
Ces notions seront abordées dans une deuxième partie.
Mais le terme « paramètre » s’entend aussi au niveau du rapport, avec les scénarios What If – « que se passerait-il si tel ou tel « paramètre » évoluait ? » -, et j’étendrai encore cette notion, en évoquant le « paramètre » permettant à l’utilisateur de choisir l’indicateur analysé (CA hors-taxe ? Ou TTC ? Ou peut-être encore la TVA ?).
Ces points seront traités dans la troisième partie du dossier.
L’utilisation la plus connue des paramètres de Power Query est la possibilité de changer rapidement de source de données : c’est très utile lorsqu’il s’agit de passer facilement d’un environnement de développement (test) à un environnement de production ; d’une base de données historisée à une base à l’instant t ; ou encore d’un fichier stocké en local à un fichier stocké sur le réseau. A condition, bien entendu, que les deux sources aient la même structure, ou du moins que les colonnes utilisées pour générer les visuels soient présentes et organisées de même manière dans les deux sources.
Un peu d’UI et une pincée de M
Il suffira pour créer ce paramètre (dans Power Query, Accueil > Gérer les paramètres > Nouveau paramètre) de renseigner manuellement les noms des instances (dans le cas d’une base) et de créer un second paramètre pour les noms des bases elles-mêmes (voir l’image ci-dessus).
Dans le cas d’un paramètre pour changer de fichier source, c’est plus simple encore. Une fois le document Power BI créé (dans mon cas, il est initialement basé sur un des deux fichiers source, Facts -2019.xlsx), il suffit de renseigner le paramètre avec le ou les chemins d’accès et le nom des fichiers :
Notez que le paramètre peut être facultatif et qu’il est typé (texte, numérique, date pour l’essentiel). Les valeurs suggérées sont soit libres (« Toutes les valeurs »), soit basées sur une liste saisie manuellement (« Liste de valeurs »), soit basées sur une liste générée à partir d’une requête (nous en verrons un exemple plus loin). Enfin une valeur par défaut et une valeur actuelle peuvent être demandées.
Une pincée de M
Attention : une fois le paramètre créé, il faut ouvrir l’éditeur avancé de la table initiale (Facts -2019.xlsx), pour modifier la ligne Source du script M :
Source = Excel.Workbook(File.Contents("C:\Users\andre\Desktop\Facts - 2019.xlsx"), null, true)
devient
Source = Excel.Workbook(File.Contents(ChoixFichier), null, true)
où ChoixFichier est le nom du paramètre.
Pour SQL Server, le script modifié ressemblera à ça :
Source = Sql.Database(SqlSrvInstance, Database, [Query="SELECT…” (etc.)
Où SqlSrvInstance, Database sont les deux paramètres.
Une fois la requête exécutée, pour passer d’un fichier à un autre, il suffit (…) d’ouvrir le menu Modifier les requêtes > Modifier les paramètres, et d’utiliser le menu déroulant pour choisir le fichier :
Et d’appliquer les modifications (bandeau jaune en haut de la page).
(Nous verrons dans la suite du dossier qu’il existe d’autres possibilités d’invoquer le paramètre).
Le petit plus : et si nous décidions de créer un titre dynamique dans le rapport en affichant le nom de la source et la date d‘actualisation ? Une démarche possible consiste, d’abord dans Power Query :
- A référencer le paramètre (clic droit > Référence)
- A convertir le résultat en table (onglet Transformer > Vers la table)
- A renommer et typer la colonne
- Et à fermer et appliquer
Dans le rapport, créer une mesure
Source_actualisation = MAX(ChoixSource[source]) & " - "& TODAY()
Et le résultat (j’utilise le visual Carte):
Dans la suite de ce dossier, nous verrons la création de deux autres types de paramètres (filtre et tri), la création de modèles de document et leur lien avec les paramètres, et la création de fonction.