Pierre Fauconnier

Salut.

Dans un récent billet, j'ai illustré la possibilité de récupérer la valeur d'une cellule nommée.
Dans le premier billet relatif aux fonctions personnalisées, nous avons vu comment créer une fonction personnalisée.

Dans ce deuxième billet consacré aux fonctions personnalisées dans Power Query, nous allons voir comment récupérer un paramètre issu d'un tableau de paramètres créé en Excel.

Plage nommée

On peut bien sûr récupérer un paramètre stocké dans une plage nommée, comme je l'explique dans ce billet. On imagine alors de pouvoir passer le nom de la plage en paramètre et récupérer la valeur de n'importe quelle plage nommée par ce biais.

Pour rappel, on récupère la valeur d'une plage nommée mono-cellule avec ce code: = Excel.CurrentWorkbook(){[Name="NomCelluleNommée"]}[Content]{0}[Column1] où NomCelluleNommée doit être remplacé par le nom de la plage nommée en Excel.

Pour reprendre l'exemple du chemin d'accès aux données saisi dans la cellule nommée Chemin, on pourrait créer une fonction qui permettra de déterminer la cellule nommée dont on veut récupérer la valeur:

Code :

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

1
2
3
4
5
let
    Source = (Name as text) as any =>
    Excel.CurrentWorkbook(){[Name=Name]}[Content]{0}[Column1]
in
    Source

La même fonction permet donc de récupérer la valeur de la première cellule (coin supérieur gauche) de n'importe quelle plage nommée du classeur. Si je crée une plage nommée Année pour pouvoir filtrer des données sur une année précise, il suffira de passer le nom de cette plage en paramètre:




Utilisation d'un tableau structuré dans la fonction personnalisée Power Query

Même si on peut trouver cela intéressant, ce n'est possible que si l'on a peu de paramètres, car il faut créer autant de cellules nommées que l'on a de paramètres à gérer. Les tableaux structurés permettant l'ajout de données qui seront reprises dans la requête Power Query, il semblera vite plus intéressant de créer le tableau des paramètres et d'utiliser une fonction qui ne récupère plus une plage nommée, mais une valeur du tableau structuré en fonction du nom de cette valeur. On disposera donc d'un tableau structuré de deux colonnes, permettant de "jouer" avec des paires Clé/Valeur.



Avec un filtre, il est alors possible de récupérer rapidement un paramètre avec le code suivant:

Code PowerQuery :

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

1
2
3
4
5
6
let
    Source = Excel.CurrentWorkbook(){[Name="t_Paramètres"]}[Content],
    #"Type modifié" = Table.TransformColumnTypes(Source,{{"Nom", type text}, {"Valeur", type any}}),
    #"Lignes filtrées" = Table.SelectRows(#"Type modifié", each ([Nom] = "Chemin"))
in
    #"Lignes filtrées"

La fonction recevra donc la clé en argument et filtrera sur cette clé, par un simple remplacement de "Chemin" par le nom du paramètre et le code ci-dessus doit être modifié pour transformer la requête Power Query en fonction paramétrée. En synthétisant un peu le code, on arrive à la fonction GetParameter suivante:

Code :

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

1
2
3
4
5
let
    Source = (Name as text) as any =>
    Table.SelectRows(Excel.CurrentWorkbook(){[Name="t_Paramètres"]}[Content], each [Nom] = Name){0}[Valeur]
in
    Source

L'ajout de paires clé/valeur dans le tableau des paramètres permet à Power Query d'exploiter ces nouveaux paramètres:








Voici un deuxième exemple d'utilisation d'une fonction paramétrée, à garder sous le coude puisque l'on peut imaginer que beaucoup de nos solutions Power Query devront utiliser les valeurs. Ainsi, si je dois récupérer les valeurs d'une d'un mois précis et d'une année précise sur base du tableau suivant, je peux tout gérer d'Excel grâce au tableau des paramètres.

Avec le tableau Excel suivant, transformé par Power Query avec l'ajout de l'année et du mois, on peut piloter l'extraction depuis Excel, en remplaçant les valeurs du filtre par les valeurs récupérées grâce à la fonction:





La modification des paramètres puis l'actualisation de la requête dans Excel ramène les nouvelles données



Dans le troisième billet, puisque nous venons de voir une requête avec l'année et le mois ajouté et donc parlé de dates, je vous montrerai comment on peut créer une table de dates grâce à une fonction un peu plus élaborée, et nous l'aborderons avec le "créateur de fonction" de Power Query.


Et vous:
allez-vous utiliser les fonctions dans vos solutions Power Query?
Trouvez-vous la création de fonctions simples facile à mettre en place?
Quelle est votre utilisation de Power Query?