Pierre Fauconnier

Salut.

Dans les billets précédents, j'ai détaillé:

1. Comment crée un paramètre Power Query pour modifier le chemin des tables d'entrée;
2. Comment créer un paramètre s'appuyant sur une liste déroulante pour augmenter la souplesse d'utilisation du paramètre;
3. Comment récupérer le chemin du fichier Excel qui contient la solution et donc utiliser un chemin relatif d'accès aux données.

Rappel de cette solution du chemin relatif

La manipulation pour cette solution consiste à:

1. créer une plage nommée dans Excel et y placer une formule qui récupère le chemin du fichier (le fichier doit avoir été enregistré;
2. créer une ligne de commande qui utilise une table importée et en extrait la première valeur⁽¹⁾;
3. incorporer cette ligne de commande comme première ligne de la requête qui doit utiliser le chemin récupéré en attribuant la valeur à une variable.

Cette manipulation doit être répétée pour chaque table présente... et à venir. Si demain, je dois ajouter une table à ma solution Power Query, je vais devoir ouvrir l'éditeur d'une requête existante, copier la ligne concernée et la coller en première ligne de la requête de traitement de la table que j'ajoute.

Pas très simple ni très convivial, cette manipulation, j'en conviens. De plus, comme toute manipulation de code, elle est potentiellement source d'erreurs. Il serait intéressant de pouvoir utiliser quelque chose comme le paramètre vu dans le premier billet: = Excel.Workbook(File.Contents(Chemin & "\Ventes.xlsx"), null, true) sans devoir passer par l'insertion de la ligne qui récupère le chemin.


Fonction qui renvoie le chemin du fichier

Dans le troisième billet, nous avons vu que nous ne pouvons pas déterminer l'ordre d'exécution des requêtes, ce qui implique qu'une requête ne peut se servir d'une autre pour récupérer les données. C'est pour cela que nous avons introduit la notion de sous-requête, car cela nous permet de dire: Tu recherches d'abord le chemin d'accès puis tu traites les données...

Pour imposer l'ordre d'exécution tout en capitalisant sur la requête de récupération du chemin relatif, nous allons créer une fonction personnalisée dans notre solution Power Query.

Revenons à la ligne de commande (l'étape Power Query) qui récupère le chemin relatif du fichier:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Table.FirstValue(Excel.CurrentWorkbook(){[Name="Chemin"]}[Content])

Nous allons créer une requête avec cette ligne de commande en partant d'Excel ou d'une requête vide (Accueil > Nouvelle requête > Nouvelle source > Autre source > Requête vide). Lorsque la requête est créée, un clic droit nous informe des possibilités de traitement de cette requête, parmi lesquelles "Créer une fonction..."⁽²⁾.



Lors du clic sur Créer une fonction, Power Query nous demande si nous voulons créer une fonction sans paramètres, puis nous demande le nom de la fonction. Notre fonction est créée. Nous remarquons qu'avec cette manière de créer une fonction, Power query regroupe les requêtes dans des dossiers⁽³⁾. Nous constatons également que la requête qui a servi à créer la fonction est conservée par Power Query. Si elle n'a pas d'autre utilité, on peut la supprimer et déplacer la fonction créée dans le dossier des autres fonctions (cela supprimera le niveau des dossiers).



Avant d'utiliser notre fonction, regardons son script au travers de l'éditeur avancé, qui nous permet de voir la syntaxe d'une fonction personnalisée Power Query.

Code Power Query :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
let
    Source = () => let
        Source = Table.FirstValue(Excel.CurrentWorkbook(){[Name="Chemin"]}[Content])
    in
        Source
in
    Source

Je détaille la façon de concevoir une fonction perso avec Power Query dans ce billet, mais je vais préciser ici que:

• la fonction n'utilise pas de paramètres car les parenthèses de Source = () => sont vides;
• la fonction n'est pas typée;
• créé de cette manière, le script est verbeux et peut être raccourci.

Pour typer la fonction, et donc ici préciser qu'elle renvoie du texte, nous pouvons utiliser as text puis nettoyer le script:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
let
    Source = () as text => Table.FirstValue(Excel.CurrentWorkbook(){[Name="Chemin"]}[Content])
in
    Source

Utilisation de la fonction dans nos requêtes

Nous allons pouvoir utiliser cette fonction dans nos requêtes presque comme un paramètre puisque comme c'est une fonction, elle requiert les parenthèses, comme dans Excel: Excel.Workbook(File.Contents(Chemin() & "\Ventes.xlsx"), null, true).

Le script de notre requête t_Ventes peut donc devenir ceci:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
let
    Données = Excel.Workbook(File.Contents(Chemin() & "\Ventes.xlsx"), null, true),
    t_Ventes_Table = Données{[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é"

Lors de l'ajout d'une requête devant utiliser ce chemin d'accès aux données, il nous suffira d'insérer la fonction dans le script de ladite requête.

Voila donc 4 billets qui permettent de mieux appréhender les notions de paramètres et de fonctions, tout en atteignant notre objectif initial: Permettre d'utiliser une donnée Excel du classeur actif pour pointer vers les tables de données à récupérer.

Ces billets vous donnent-ils des idées pour vos propres développements?
Utilisiez-vous déjà les paramètres et les fonctions perso dans Power Query?
Souhaitez-vous d'autres billets sur Power Query?




(1) Michaël (mfoxy) proposait dans une réponse à mon précédent billet une syntaxe que je préfère: Excel.CurrentWorkbook(){[Name="CelNommeeChemin"]}[Content]{0}[Column1] car elle correspond mieux à une syntaxe POO et colle mieux avec le code VBA que j'utilise pour pointer vers une cellule particulière d'un tabeau structuré. Chacun choisira la méthode qui lui convient le mieux.

(2) Perso, je passe par l'éditeur avancé et je code moi-même les lignes qui transforment la requête en fonction