Pierre Fauconnier

Salut.

Dans un récent billet, je montrais comment utiliser une cellule nommée dans une requête Power Query.

Puis dans ce billet, nous avons vu comment créer une fonction et y ajouter des paramètres.

Pour rendre notre modèle plus malléable, j'ai montré dans ce billet comment utiliser un tableau de paires Clé/Valeur au sein d'une fonction.

La façon illustrée dans ces billets amène à devoir modifier le code de la fonction pour la faire évoluer. C'est un peu dans l'ordre des choses, bien entendu, et peut-être pertinent pour des fonctions simples qui renvoient une valeur. Cette limite pourrait cependant s'avérer gênante lorsque la fonction demande plus d'étapes ou qu'elle renvoie une table à laquelle on voudrait ajouter rapidement des colonnes. C'est le cas typique d'une fonction qui renvoie une table de dates comprenant plusieurs colonnes permettant de manipuler les propriétés d'une date (Année, Mois, jour de la semaine, etc).

Dans ce billet, nous allons donc apprendre trois choses:

1. Comment créer une table de valeurs directement dans Power Query;
2. Comment créer une fonction qui renvoie une table;
3. Comment transformer une requête complexe en fonction pour en visualiser les étapes et pouvoir en ajouter facilement via l'interface Power Query plutôt que par code.

Création d'une table de dates

On crée une table de dates en trois étapes:

1. Création d'une liste de dates;
2. Transformation en table;
3. Ajout des colonnes souhaitées.

Pour créer une liste de dates, Power Query expose la méthode List.Dates(Début, Nombre, Incrément). On remarque de suite que le second argument n'est pas la date de fin, mais le nombre de lignes à créer. Il faudra donc calculer ce nombre si l'on souhaite créer une fonction qui va "de date à date".

La requête suivante crée une liste de 366 jours allant du 01/01/2020 au 31/12/2020 par pas de 1 jour: = List.Dates(#date(2020,1,1),366,#duration(1,0,0,0)). On remarque comment passer une date sur base de ses trois composantes, et que le pas de progression est exprimé en duration. On pourrait donc créer une plage avec des écarts de 1 heure en passant un #duration(0,1,0,0), par exemple.

Bien sûr, on pourrait rapidement créer une fonction qui récupère les deux premiers paramètres et crée la liste:

Code :

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

1
2
3
4
5
let
    Source = (FirstDate as date, CountOfDays as number) =>
    List.Dates(FirstDate,CountOfDays,#duration(1,0,0,0))
in
    Source

Cette fonction pourrait s'utiliser comme ceci pour récupérer les 31 jours de décembre 2020:


Ici, nous allons complexifier la requête pour que

1. le nombre de jours soit calculé par rapport aux dates de début et de fin;
2. ce soit une table et pas une liste qui soit retournée;
3. cette table contienne quelques propriétés d'une date (année, mois, jour).

Pour ce faire, nous allons d'abord créer la requête en la basant sur deux paramètres Power Query.

Pour créer un paramètre, Accueil\Paramètres\Créer un paramètre:


Lorsque nos paramètres sont créés, nous allons créer une nouvelle requête vide et utiliser nos paramètres pour créer la liste. La première étape de la requête consiste à créer la liste. Pour cela, nous avons besoin du nombre de jours, qui s'obtient en retranchant la date de début de la date de fin. Avec Excel, nous pourrions en rester là car les dates sont des nombres. Avec Power Query, des dates sont... des dates, et calculer le nombre de jours entre deux dates revient à calculer une durée. Cette opération renvoie donc un objet duration que nous transformons en number grâce à Number.From(...) => .



C'est le seul code que nous allons réellement saisir, la suite des opérations pouvant être effectuée par l'interface.

Transformons la liste en table grâce à l'outil Power Query prévu, en activant la liste puis Outils Liste\Transformer\Convertir\Vers la table... On renommera la colonne en Date et on transformera son type, là aussi grâce aux outils Power Query (double-clic sur l'intitulé de la date pour renommer la colonne, puis clic droit sur l'intitulé pour modifier le type).



En sélectionnant à chaque fois la date, on peut ajouter les colonnes via l'interface => Ajouter une colonne\Date et heure de début\Date\...




Transformation de cette requête en fonction

Pour transformer cette requête en fonction, il suffira d'un clic droit effectué sur le nom de la requête dans le panneau de gauche. La fonction utilisant des paramètres, il suffit de mentionner son nom, par exemple getTableOfDates et la fonction est créée:





Cette fonction est créée par l'interface et stockée dans un dossier qui contient ce dont elle a besoin pour rendre les services attendus. Elle s'utilise comme n'importe quelle fonction comme le montre l'illustration ci-dessus.

Cette fonction est en fait liée à la requête qui se trouve dans le même dossier, et si nous cliquons sur cette requête, nous visualisons ses étapes dans le panneau de droite. Et comme tout requête, nous pouvons en modifier les étapes, en supprimer, mais aussi en ajouter. Alors que la fonction expose pour l'instant 4 colonnes (date, année, mois, jour), nous pouvons modifier la fonction en modifiant la requête, par exemple pour ajouter le jour de la semaine en nombre et en lettres, et ajouter aussi le numéro de la semaine. Il suffit d'ajouter les colonnes exactement comme nous l'avons fait pour créer les colonnes Année, Mois et Jour. Notons bien ici que c'est la requête liée à la fonction qui est modifiée et à laquelle on ajoute les colonnes.




Maintenant, si l'on souhaite créer une table de dates, elle inclura automatiquement les nouvelles données: