Pierre Fauconnier

Salut.

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

• Dans le premier billet de cette trilogie, je vous proposais de rendre le chemin d'accès aux tables d'entrée paramétrable;
• Dans le deuxième billet, j'illustrais comment créer une liste déroulante pour choisir le paramètres parmi plusieurs valeurs possible.

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





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⁽¹⁾. 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:



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.




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é)⁽²⁾.

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)




Et voilà notre chemin relatif utilisable dans Power Query.

Résultat des données pour la Belgique



Et le résultat pour la France




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?


⁽¹⁾ 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.

⁽²⁾ 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.