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

Pierre Fauconnier

Power Query: Chemin relatif au départ d'Excel (3)

Noter ce billet
par , 27/11/2020 à 14h20 (756 Affichages)
Salut.

Ce billet est le troisième d'une quadrilogie consacrée à la paramétrisation du chemin d'accès aux données:



Mais ces paramètres sont modifiables uniquement dans l'environnement Power Query. Une demande récurrente est de pouvoir modifier le chemin d'accès au départ d'Excel soit pour faciliter la vie de l'utilisateur, soit pour éviter d'entrer dans Power Query et de risquer d'y causer des dégâts, soit parce que l'on crée une solution Power Query générique que l'on souhaite pouvoir déployer dans d'autres dossiers, chez d'autres utilisateurs. Nous allons voir qu'en travaillant sur le script de la requête, nous pouvons arriver à nos fins, et même développer une solution Power Query qui fonctionne avec un chemin relatif pour l'accès aux donneés.

Paramètre dans une plage nommée

Power Query permet de récupérer des données d'Excel sous trois formes:
  1. Tableau structuré, table de données ou tableau dynamique (voir mon tuto à ce sujet);
  2. Plage nommée (essentiellement pour des valeurs uniques);
  3. Feuille de calcul (à bannir selon moi, mais on doit parfois faire avec).


Pour les plages nommées et les feuilles de calcul, il faut qu'elles ne disposent pas de filtres. On notera également qu'au départ d'Excel, Power Query transformera en tableau la plage sélectionnée si elle n'est ni un tableau ni une plage nommée.

Dans les précédents billets cités ci-dessus, j'évoquais l'idée de pouvoir créer des requêtes au départ de dossiers différents selon l'emplacement géographique du bureau qui utilise la solution Power Query. Je poursuis donc cette idée ici en saisissant un chemin d'accès dans une cellule nomméeChemin. Lors de l'attribution du nom à la cellule, n'oubliez pas le ENTER...

Nom : 2020-11-27_100119.png
Affichages : 94
Taille : 4,6 Ko

Nom : 2020-11-27_102101.png
Affichages : 75
Taille : 7,2 Ko

Il faut noter que lors de l'incorporation d'une plage nommée monocellule au départ d'Excel, Power Query crée une table en promouvant la première et unique ligne de la plage nommée, ce qui aboutit à une table vide. Il faut donc supprimer les étapes de promotion du titre pour ne garder que la première, appelée par défaut Source.

Nous avons à notre disposition une table, mais nous souhaitons avoir une valeur unique... Pour cela, nous souhaitons récupérer la première et unique valeur de cette table(1). Nous allons ajouter une étape personnalisée à notre requête (clic droit sur l'étape Source puis Insérer l'étape après) et nous allons la transformer "à la main". Cette nouvelle étape, basée sur l'étape Source expose comme formule = Source
L'objet Table expose une série de possibilités de traitements, que nous pouvons voir en notation pointée dans la zone de saisie de la commande. Ce lien Microsoft vous donne tous les détails de l'objet ( en français - en anglais ).

Pour récupérer la première valeur d'une table, c'est-à-dire la valeur de la première colonne de la première ligne, on peut utiliser la méthode FirstValue:

Nom : 2020-11-27_104402.png
Affichages : 70
Taille : 5,6 Ko

L'observateur attentif remarquera que l'icône à gauche de notre requête Chemin a été modifiée et exprime maintenant que Chemin n'est plus une table, mais une valeur de type text.

A ce stade, il serait évidemment très tentant d'utiliser cette valeur à la place de notre paramètre figé, et on aurait notre chemin dynamique au départ d'Excel. (Vous avez remarqué le conditionnel dans "il serait?)

Essayons. Dans notre requête des ventes, nous pouvons modifier l'étape Source pour y introduire notre valeur dynamique.

Nom : 2020-11-27_105109.png
Affichages : 71
Taille : 3,9 Ko


Et Bardaf, c'est l'embardée! Un message peu compréhensible apparait et nous indique, pour faire simple, qu'une requête ne peut pas utiliser une autre requête et qu'il faut "reconstruire cette combinaison de données"...

Hé oui, dans Power Query, une requête ne peut pas faire appel à une autre requête pour déterminer une source externe de données. Cela imposerait un ordonnancement de l'exécution des requêtes et ce n'est pas prévu. En revanche, une requête peut utiliser une sous-requête. Cela signifie que l'on peut insérer dans une requête une étape, ou plusieurs, qui prépare une sous-requête. Cette sous-requête ne sera bien entendu pas utilisable en dehors de la requête qui la contient. Nous allons insérer une étape dans notre requête t_Ventes avant l'étape Source. Toutefois, il faudra coder car l'interface ne permet pas la manip.

Revenons à notre requête Chemin pour examiner son script au travers de l'éditeur avancé (Accueil > Requête > Editeur avancé)(2).

Code power query : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
let
    Source = Excel.CurrentWorkbook(){[Name="Chemin"]}[Content],
    Personnalisé1 = Table.FirstValue(Source)
in
    Personnalisé1

On y retrouve les deux étapes de notre requête, Source et Personnalisé1. Pour plus de concision, fusionnons ces deux étapes en une seule et appelons-là Chemin

Code Power Query : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
let
    Chemin = Table.FirstValue(Excel.CurrentWorkbook(){[Name="Chemin"]}[Content])
in
    Chemin

Cette étape Chemin = Table.FirstValue(Excel.CurrentWorkbook(){[Name="Chemin"]}[Content]) pourrait devenir la toute première étape de nos requêtes t_Ventes et t_Vendeurs, de manière à ce que les étapes suivantes de ces requêtes puissent utiliser Chemin, en considérant que Chemin est une variable que l'on utilisera par la suite dans la requête.

Si l'on observe les premières lignes du script de t_Ventes, on va voir où l'on peut insérer cette ligne de code.

Code Power Query : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
let
    Source = Excel.Workbook(File.Contents(Chemin & "\Ventes.xlsx"), null, true),
    t_Ventes_Table = Source{[Item="t_Ventes",Kind="Table"]}[Data],
    #"Type modifié" = Table.TransformColumnTypes(t_Ventes_Table,{{"Vendeur", type text}, {"Date", type date}, {"Montant", type number}})
in
    #"Type modifié"

Puisque Source utilise Chemin, nous allons définir Chemin juste avant. Attention, n'oubliez pas la virgule, qui sépare les étapes de la requête. Le script de t_Ventes devient:
Code Power Query : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
let
    Chemin = Table.FirstValue(Excel.CurrentWorkbook(){[Name="Chemin"]}[Content]),
    Source = Excel.Workbook(File.Contents(Chemin & "\Ventes.xlsx"), null, true),
    t_Ventes_Table = Source{[Item="t_Ventes",Kind="Table"]}[Data],
    #"Type modifié" = Table.TransformColumnTypes(t_Ventes_Table,{{"Vendeur", type text}, {"Date", type date}, {"Montant", type number}})
in
    #"Type modifié"

Il suffit d'insérer également cette ligne dans les autres requêtes de notre solution et nous pourrons modifier le chemin d'accès aux tables directement dans Excel! Nous avons maintenant la possibilité de modifier dynamiquement nos chemins d'accès au travers d'Excel, en modifiant la valeur de la plage nommée puis en actualisant le jeu de requêtes.

En résumé:
  1. Créer une plage nommée dans Excel;
  2. L'importer dans Power Query comme une requête;
  3. Inclure la commande d'acquisition de cette source dans une commande Table.FirstValue(MonParamètre);
  4. Inclure cette commande adaptée comme première source des requêtes dont le paramètre doit être dynamique;
  5. Modifier le reste du script de la requête pour remplacer les données "hardcodées" par la valeur récupérée sur la première ligne.



Oui mais, si la valeur de la plage nommée est le résultat d'une formule? Cela ne pose aucun problème. Power Query récupère la valeur calculée et non la formule... Et donc, le paramètre envoyé dans Power Query peut lui-même être calculé.


Chemin relatif d'accès aux tables


Dès lors, en convenant que les tables sont placées dans un dossier de l'arborescence du classeur qui contient la solution Power Query, on peut rendre le chemin d'accès aux données dépendant du classeur. Il faut pour cela savoir comment on récupère le chemin d'un classeur dans une cellule...

Pour retrouver dans une cellule le chemin d'accès du fichier, on va se tourner vers Excel et formuler un peu . Pour autant que le classeur ait été enregistré, son nom complet incluant le chemin d'accès peut être récupéré par la formule =CELLULE("nomfichier"). On récupère ainsi le chemin, le nom du fichier entre crochets puis le nom de la feuille. Par exemple: C:\data\Temp\Power Query Belgique\[PowerQuery-Relatif.xlsx]Feuil11.

Dans Excel, on pourra donc récupérer ce qui se trouve à gauche du crochet ouvrant: =GAUCHE(CELLULE("nomfichier");CHERCHE("[";CELLULE("nomfichier"))-2)
Nom : 2020-11-27_130854.png
Affichages : 72
Taille : 3,8 Ko



Et voilà notre chemin relatif utilisable dans Power Query.

Résultat des données pour la Belgique

Nom : 2020-11-27_131004.png
Affichages : 78
Taille : 98,6 Ko

Et le résultat pour la France

Nom : 2020-11-27_131114.png
Affichages : 73
Taille : 99,0 Ko


Bien sûr, on peut également utiliser les plages nommées et les tableaux pour gérer d'autres paramètres de Power Query (le tableau vers lequel pointer, la colonne à prendre en considération pour un calcul, etc, etc...)

Dans le quatrième billet
, je vous montre comment créer une fonction personnalisée Power Query qui vous permettra d'utiliser ce chemin relatif dans les nouvelles tables de la solution Power Query.

Intéressé par ces approches?
Power Query va-t-il entrer dans votre vie?
Ces trois billets vous donnent-ils des idées?
Vous permettent-ils d'avancer dans votre utilisation de Power Query?


(1) Notez bien ici que les valeurs d'entrées arrivent dans Power Query sous forme de table. C'est à nous qu'il appartient de les transformer en liste ou en valeurs uniques.

(2) L'éditeur avancé permet de visualiser et de modifier tout le script d'une requête. C'est en quelque sorte la fenêtre d'édition qui va nous permettre de modifier notre script, voire de le créer de toutes pièces.

Envoyer le billet « Power Query: Chemin relatif au départ d'Excel (3) » dans le blog Viadeo Envoyer le billet « Power Query: Chemin relatif au départ d'Excel (3) » dans le blog Twitter Envoyer le billet « Power Query: Chemin relatif au départ d'Excel (3) » dans le blog Google Envoyer le billet « Power Query: Chemin relatif au départ d'Excel (3) » dans le blog Facebook Envoyer le billet « Power Query: Chemin relatif au départ d'Excel (3) » dans le blog Digg Envoyer le billet « Power Query: Chemin relatif au départ d'Excel (3) » dans le blog Delicious Envoyer le billet « Power Query: Chemin relatif au départ d'Excel (3) » dans le blog MySpace Envoyer le billet « Power Query: Chemin relatif au départ d'Excel (3) » dans le blog Yahoo

Commentaires

  1. Avatar de mfoxy
    • |
    • permalink
    Hello Pierre,

    Encore une belle série de nouveaux tutos dans ton blog ces derniers temps,bravo.

    Pour ce dernier, je pense que l'on pourrait même ce passer d'une "pseudo Table",pour l'import d'une cellule nommée, utilisant un code du genre ( de mémoire) :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    Chemin = Excel.CurrentWorkbook(){[Name="CelNommeeChemin"]}[Content]{0}[Column1],
    (voir même créer un Function PowerQuery, GetNamedCel() avec comme argument le nom de la cellule)

    Au plaisir,
    Michael
  2. Avatar de Pierre Fauconnier
    • |
    • permalink
    Salut Michaël...

    Arf, tu as vendu la mèche. Un de mes tout prochain billet montrera comment créer une fonction Power Query...

    Quant à ta solution, j'aime vraiment bien. Je suis parti sur FirstValue, mais le {0}[Column1], j'aime vraiment bien car c'est un peu le pendant Power Query de ce que je préconise en VBA lorsque l'on travaille avec une référence structurée... Range("MonTableau[MaColonne]")(x)... Donc j'adhère!

    Avoir plusieurs manières de procéder permet d'abord de ne pas se figer dans ses certitudes, mais aussi d'avoir plusieurs solutions en fonction des cas qui se posent.

    Vraiment, merci pour ta réaction et ta solution
  3. Avatar de mfoxy
    • |
    • permalink
    Re,

    Désolé d'avoir vendu la mèche, lol. Mais tu sais bien que je suis de nature à aimer le réutilisable, même le PowerQuery peut-être recyclé.

    Pour le Vba un sage a dit, il faut penser Excel avant de penser Vba, pour le PQ, je modifierais un peu en disant : il faut apprendre à s'en servir, avant de pouvoir utiliser un minimum de "morceau" ( on parle bien de Power Query, hein...).

    Perso, tout comme pour Vba, mes Fonctions génériques PwrQry sont embarquées dans le Template de base de mes développements, avec mes modules Vba génériques (xlTable,xlRow,xlDate ,xlMail,xlIE...) et je retire les modules et fct PQ non nécessaire, avant mise en production du fichier.

    J'attends ton prochain billet avec impatience, maintenant qu'il est annoncé.

    A bientôt,
    Michael
    Mis à jour 27/11/2020 à 22h03 par mfoxy