Power Query: Chemin dynamique grâce à l'utilisation d'un paramètre (1)
par
, 27/11/2020 à 08h00 (9941 Affichages)
Salut.
Après avoir créé une solution Power Query, on peut avoir besoin de modifier rapidement certaines infos qui sont utilisées par plusieurs requêtes:
- Basculer d'un environnement de développement à un environnement de production;
- Déployer la solution Power query pour plusieurs utilisateurs qui utilisent des arborescences différentes, dans des filiales étrangères;
- Déployer chez des clients différents une solution Power Query générique;
- ...
Prenons le cas d'un déploiement d'une solution Power Query pour des bureaux en Belgique que l'on souhaitera pouvoir utiliser pour les bureaux français qui, bien sûr, n'utilisent pas une arborescence de dossiers identiques. On imagine qu'après le déploiement en France, les espagnols voudront la même, puis les italiens, etc. C'est là que le paramètre va entrer en jeu.
Voici un jeu de requêtes Power Query qui fusionnent une table des ventes avec une tables des vendeurs, de manière à calculer la commission et le résultat après commission. Il s'appuie sur deux tableaux t_Ventes et t_Vendeurs qui se trouvent dans deux classeurs différents. Les classeurs sont présents dans le même dossier, parmi une foule d'autres classeurs et de sous-dossiers.
Le résultat de la fusion avec le calcul de la commission pourrait se présenter ainsi dans Power Query
On remarque que la source des requêtes utilise une donnée commune, à savoir le chemin d'accès aux données
Pour déployer cette solution Power Query sur le site français, il va falloir aller modifier à la main le chemin dans l'étape d'acquisition des données, appelée Source par défaut dans Power Query. Bien sûr, pour un One Shot, ce n'est pas très grave, surtout avec seulement deux tables en entrée. Mais comme après la France, votre solution sur base de 50 tables en entrée sera déployée en Espagne, puis en Italie, puis au Portugal, ça va devenir coton, surtout que vous allez bien sûr améliorer votre solution Power Query et devoir la redéployer en réalisant à nouveau les modifications de chemin.
C'est à ce moment que les paramètres entrent en jeu. On peut les considérer comme des constantes que l'on va utiliser dans nos lignes de commande. Il suffira alors de modifier la valeur du paramètre pour que les requêtes pointent vers le nouvel environnement(1).
Création du paramètre
Un paramètre se crée dans Power Query via l'onglet Accueil > Paramètres > Gérer les paramètres > Nouveau paramètres. Il faut renseigner le nom, le type et la valeur dans la fenêtre de dialogue qui s'ouvre et le paramètre est créé.
Utilisation du paramètre
Il faut maintenant remplacer le chemin hardcodé (écrit en toutes lettres) dans la commande qui récupère les données du classeur.Pour cela, pas de miracles, il va falloir "taper du code"... Juste un petit peu.
Je remontre ici la ligne de commande de l'étape d'acquisition de données dans un autre classeur Excel.
Pour utiliser le paramètre dans cette ligne de commande, on va remplacer le chemin par le nom du paramètre et concaténer cette valeur avec le reste du nom du fichier(2). L'opérateur de concaténation dans Power Query est le caractère &, comme dans Excel. On remarque que la syntaxe est identique à celle que l'on aurait utilisée dans Excel pour concaténer la valeur d'une cellule nommée avec une partie fixe de texte. Notez bien ici que le nom du paramètre n'est pas encadré par des guillemets, et souvenez-vous que Power Query est sensible à la casse(3).
l'opération sera répétée pour les autres requêtes qui utilisent la valeur du paramètre.
On remarquera que, côté Excel, le paramètre est disponible via le panneau latéral des connexions (Onglet Données > Requêtes et connexions > Requêtes et connexions)
Modification du paramètre
Il suffira de modifier la valeur du paramètre puis d'actualiser la solution pour que Power Query pointe vers les fichiers français, espagnols et autres. Cette modification doit être effectuée dans Power Query (par exemple, double-clic sur le paramètre dans le panneau de connexion).
Et voilà le travail! La simplicité est une notion toute relative, mais il me semble que la mise en place de la solution peut faire gagner beaucoup de temps.
Bien sûr, ces paramètres sont statiques, leur modification implique de rentrer dans Power Query et la saisie à la main est potentiellement génératrice d'erreurs.
Dans le deuxième billet, je vous montrerai comment utiliser une liste déroulante dans un paramètre, histoire de donner un peu plus de dynamisme...
Dans le troisième billet, nous verrons comment utiliser une sous-requête pour utiliser le chemin relatif du fichier Power Query pour récupérer nos données;
Dans le quatrième billet, nous approcherons la notion de fonction personnalisée pour réutiliser le chemin relatif dans toutes les requêtes qui doivent l'utiliser;
(1) Ce présent billet ne prétend pas à l'exhaustivité sur le sujet du paramètre dans Power Query
(2) Vous pouvez également passer par l'éditeur avancé qui ouvre une page de saisie offrant le texte complet de la requête Power Query. C'est une bonne chose pour se familiariser avec les syntaxes Power Query que d'étudier régulièrement le code qui est produit par "l'enregistreur" de Power Query lorque vous créez votre requête via l'interface.
(3) Pour nous qui venons du monde Excel et VBA, il est perturbant, au départ, de constater que Chemin et chemin sont deux choses différentes pour Power Query, et de nombreuses erreurs "de débutant" proviennent de l'absence de différenciation entre majuscules et minuscules.