IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Voir le flux RSS

Pierre Fauconnier

Power Query: Chemin dynamique grâce à l'utilisation d'un paramètre (1)

Note : 2 votes pour une moyenne de 3,00.
par , 27/11/2020 à 08h00 (3525 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.

Nom : 2020-11-26_213715.png
Affichages : 1179
Taille : 169,6 Ko

Le résultat de la fusion avec le calcul de la commission pourrait se présenter ainsi dans Power Query

Nom : 2020-11-26_214827.png
Affichages : 1132
Taille : 78,4 Ko

On remarque que la source des requêtes utilise une donnée commune, à savoir le chemin d'accès aux données

Nom : 2020-11-26_215302.png
Affichages : 1137
Taille : 86,2 Ko

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

Nom : 2020-11-26_220339.png
Affichages : 1125
Taille : 10,5 Ko

Nom : 2020-11-26_220410.png
Affichages : 1109
Taille : 4,1 Ko



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.

Nom : 2020-11-27_053909.png
Affichages : 1104
Taille : 2,8 Ko

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

Nom : 2020-11-27_054239.png
Affichages : 1117
Taille : 7,3 Ko

Nom : 2020-11-27_054521.png
Affichages : 1102
Taille : 2,7 Ko

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)

Nom : 2020-11-27_060706.png
Affichages : 1163
Taille : 243,7 Ko



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

Nom : 2020-11-27_061449.png
Affichages : 1111
Taille : 3,9 Ko

Nom : 2020-11-27_061506.png
Affichages : 1129
Taille : 66,4 Ko

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.

Envoyer le billet « Power Query: Chemin dynamique grâce à l'utilisation d'un paramètre (1) » dans le blog Viadeo Envoyer le billet « Power Query: Chemin dynamique grâce à l'utilisation d'un paramètre (1) » dans le blog Twitter Envoyer le billet « Power Query: Chemin dynamique grâce à l'utilisation d'un paramètre (1) » dans le blog Google Envoyer le billet « Power Query: Chemin dynamique grâce à l'utilisation d'un paramètre (1) » dans le blog Facebook Envoyer le billet « Power Query: Chemin dynamique grâce à l'utilisation d'un paramètre (1) » dans le blog Digg Envoyer le billet « Power Query: Chemin dynamique grâce à l'utilisation d'un paramètre (1) » dans le blog Delicious Envoyer le billet « Power Query: Chemin dynamique grâce à l'utilisation d'un paramètre (1) » dans le blog MySpace Envoyer le billet « Power Query: Chemin dynamique grâce à l'utilisation d'un paramètre (1) » dans le blog Yahoo

Commentaires

  1. Avatar de Pierre Dumas
    • |
    • permalink
    Bonjour Pierre

    Lecture très intéressante. Merci à toi.

    Bonne fin de journée

    Pierre Dumas
    Mis à jour 08/05/2021 à 03h01 par Malick (Coquilles corrigées)
  2. Avatar de Malick
    • |
    • permalink
    Salut Pierre,

    Je ne peux que te remercier et féliciter pour cette astuce que je viens de mettre en pratique pour un de mes fichiers

    bravo:
  3. Avatar de Malick
    • |
    • permalink
    Salut Pierre,

    L'exemple porte sur un fichier, mais s'il s'agit d'un dossier contenant plusieurs fichiers à importer, comment procéder ?

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    = Folder.Files("C:\Users\Malick\Documents\Perso\TimeSheets\2022\Janvier2022")
    Avec ceci, cela fonctionne bien :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    = Folder.Files(CheminDossierImport)
    Est-ce la bonne démarche ?

    Merci