Power Query le pouvoir des fonctions.

Vous avez la même intégration de données à répéter ? Par exemple un dossier avec des fichiers Excel au même format ? Power Query vous simplifie la vie avec les fonctions.

Contexte

Power Query est un outil formidable pour automatiser vos préparations de données.
Mais souvent vous vous retrouvez à refaire les mêmes requêtes avec juste un paramètre qui change, c’est à ce moment que vous devez apprendre les fonctions afin d’automatiser ce type de tâche.

Pour bien comprendre cet article il est recommandé de maitriser l’éditeur avancé de Power Query présenter dans l’article Bien utiliser l’éditeur avancé de Power Query

Cas d’usage

Les cas d’usages des fonctions sont multiples, voici quelques exemples classiques à garder en tête :

Appliquer un même ensemble de transformations sur des fichiers, Excel par exemple, contenu dans un dossier.
Appliquer un même ensemble de transformations sur les feuilles d’un fichier Excel.
Appliquer un ensemble de transformations dans plusieurs requêtes Power Query différentes.

Les exemples ci-dessus ont tous en commun l’usage de fonction.
En informatique une fonction est une portion de code réalisant une tâche spécifique.
Une fonction est une requête Power Query spécifique, elle est donc :

Écrite et maintenue en dehors des autres requêtes Power Query.
Appelable par n’importe quelles autres requêtes Power Query.
Elle peut prendre ou non des paramètres en entrée afin de variabiliser son exécution.

Exemple

Imaginons le traitement dans lequel vous souhaitez intégrer un flux RSS sous la forme d’une table dans Power Query. Les étapes de transformation seront les suivantes :

Le code brut Power Query est le suivant :

1
2
3
4
5
6


let
    Source = Xml.Tables(Web.Contents("https://www.nasa.gov/rss/dyn/breaking_news.rss")),
    channel = Source{0}[channel],
    item = channel{0}[item]
in
    item

Maintenant que nous avons fait cette première intégration, nous souhaitons ajoute de nouveaux flux RSS en plus dans la même table. Nous avons 2 manières pour faire cela.

Sans l’usage d’une fonction

Vous devrez

Créer une requête par flux RSS
Fusionner toutes les requêtes ensemble.

Cette solution est fastidieuse et difficilement maintenable.

Avec une fonction Power Query

Nous allons avoir besoin de seulement 2 requêtes Power Query :

Une requête contenant la fonction
Une requête contenant la liste des flux RSS que l’on souhaite traiter.

Nous allons donc commencer par transformer le code précédent en fonction. Pour faire cela, nous devons analyser le code et réfléchir à l’automatisation que l’on veut mettre en place.
Les numéros de lignes que je cite correspondent à ceux visibles dans l’éditeur avancé de la copie d’écran précédente.

Sur la ligne 2, on lit le contenu d’une URL via la fonction Web.Contents et on envoie le résultat de cette lecture à la fonction Xml.Tables afin d’interpréter le résultat en tant que table XML. Le résultat est stocké dans Source.
Sur la ligne 3, on lit la colonne channel ,via [channel], de la première ligne, via {0}, de Source. En Power Query la première ligne à l’indice zéro. Le résultat est stocké dans channel.
Sur la ligne 4, on lit la colonne item ,via [item], de la première ligne, via {0}, de channel. Le résultat est stocké dans item.
Le contenu de item est renvoyé par la requête Power Query.

Maintenant que nous avons analysé le code, on se rend compte que si on variabilise l’URL, on peut répéter les autres opérations sur des données ayant la même structure.

La fonction Power Query sera donc la suivante :

Le code brut Power Query est le suivant :

1
2
3
4
5
6
7
8
9


let readFeed = (URLFeed) =>
    let
        Source = Xml.Tables(Web.Contents(URLFeed)),
        channel = Source{0}[channel],
        item = channel{0}[item]
    in
        item
in
    readFeed

Sur la ligne 1, nous déclarons la fonction readFeed qui prend comme paramètre URLFeed.
Le bloc de code entre la ligne 2 et 7 correspond à notre code de l’exemple initial, vous remarquerez sur la ligne 3 une modification, on a remplacé la valeur de l’URL par la valeur du paramètre URLFeed de la fonction.
Sur les lignes 8 et 9 la fonction renvoie la valeur retournée par le bloc de code entre les lignes 2 et 7.

Pour tester votre fonction, sélectionnez-la.
Remplir le paramètre avec la valeur souhaitée.
Appuyez sur le bouton Appeler.
Une nouvelle requête est créée avec l’appel de la fonction et son résultat.

⚠ Notez bien que la requête Power Query qui contient la fonction s’appelle fctNasaFeed, comme on le voit dans la liste des requêtes. Le nom de la fonction readFeed utiliser dans le code Power Query n’est accessible que dans le code Power Query, les appels de la fonction par d’autres requêtes Power Query ce fera par le nom de la requête fctNasaFeed.

Testez toujours votre fonction une fois pour être sûr que cette dernière fonctionne.

Nous allons maintenant utiliser notre fonction sur un ensemble d’URL, pour faire cela nous avons besoin de créer une table contenant les données sur lesquelles on souhaite appliquer la fonction.

Le code brut Power Query est le suivant :

1
2
3
4
5


let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jc0xD8IgEIbhv0KYG9gdjQ4uOjiS5nIKpUQ9TI9K+PdiqyZOdf3y3nPGyPXg8BLIi73LLBvZp3TnldY5Z0XIqHx86IFZ20L69I6BaqzqKtvGyK0dz5hCpL8M96l/jN0NvROxE6l3YoNlSbl6CK8TiB3UE7BYvtaBJuWYpjdLUqQa89xCHWemfQI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Nom = _t, Feed = _t]),
    #"Type modifié" = Table.TransformColumnTypes(Source,{{"Nom", type text}, {"Feed", type text}})
in
    #"Type modifié"

Cette table, créer avec via l’option Entrer des données du menu Accueil, contient 2 colonnes, une avec le nom du flux et la seconde avec l’URL.

Nous allons maintenant appliquer la fonction sur chaque ligne de cette table :

Sélectionnez la requête FeedList créer avec le code ci-dessus.
Allez dans le menu Ajouter une colonne.
Sélectionnez Colonne personnalisée.
Saisissez un nom pour votre nouvelle colonne.
Rentrez le code fctNasaFeed([Feed]) pour appeler la fonction.
Appuyez sur OK pour valider.

La fonction sera appelée pour chaque ligne de la table en utilisant la valeur de la colonne [Feed] comme paramètre de la fonction.

Le résultat est donc le suivant :

Vous pouvez constater que nous avons une table dans chaque cellule de la nouvelle colonne. C’est le résultat de la fonction.
Pour voir le contenu d’une table, cliquer dans la zone blanche de la cellule, pas sur le texte table.
Vous voyez en bas le contenu de la table.

Si vous cliquez sur le texte table, une nouvelle étape et automatiquement créer avec le contenu de la table et vous devrez supprimer manuellement cette étape pour continuer cet exemple.

Nous allons maintenant obtenir une table avec l’ensemble des valeurs.

Appuyez sur le bouton développé à droite de l’entête de la colonne.
Sélectionner les champs que vous souhaitez récupérer.
Désélectionner Utiliser le nom de la colonne d’origine comme préfixe car nous n’avons pas de risque de nom de colonne en double.
Appuyez sur OK pour valider.

Vous avez maintenant une table avec l’ensemble des données en provenance des différents flux RSS, le tout appelé à l’aide d’une seul fonction Power Query.

Le code final brut Power Query est le suivant :

1
2
3
4
5
6
7


let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jc0xD8IgEIbhv0KYG9gdjQ4uOjiS5nIKpUQ9TI9K+PdiqyZOdf3y3nPGyPXg8BLIi73LLBvZp3TnldY5Z0XIqHx86IFZ20L69I6BaqzqKtvGyK0dz5hCpL8M96l/jN0NvROxE6l3YoNlSbl6CK8TiB3UE7BYvtaBJuWYpjdLUqQa89xCHWemfQI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Nom = _t, Feed = _t]),
    #"Type modifié" = Table.TransformColumnTypes(Source,{{"Nom", type text}, {"Feed", type text}}),
    #"Personnalisée ajoutée" = Table.AddColumn(#"Type modifié", "Retour de la fonction", each fctNasaFeed([Feed])),
    #"Retour de la fonction développé" = Table.ExpandTableColumn(#"Personnalisée ajoutée", "Retour de la fonction", {"title", "link", "description", "enclosure", "guid", "pubDate", "source", "http://purl.org/dc/elements/1.1/"}, {"title", "link", "description", "enclosure", "guid", "pubDate", "source", "http://purl.org/dc/elements/1.1/"})
in
    #"Retour de la fonction développé"

Aller plus loin avec les fonctions

Table utiliser pour appeler la fonction

Comme nous l’avons vu dans l’exemple ci-dessus, pour utiliser une fonction, il faut avoir une table sur laquelle l’appliquer. Classiquement cette table peut être :

La liste des fichiers d’un dossier et ses sous-dossiers, locaux ou dans SharePoint.
La liste des onglets d’un classeur Excel, cette liste est disponible dans l’étape suivant l’ouverture du fichier Excel dans Power Query (le résultat de la fonction Power Query Excel.Workbook).
La liste des tables d’une base de données.

Bien entendu cette liste n’est pas exhaustive.

Valeur en sortie de la fonction

Dans notre exemple, la fonction a renvoyé une table. Bien entendu, une fonction peut renvoyer tous les types de données supportées en sortie d’une requête Power Query, comme une chaine de caractère, une date ou une valeur numérique.

Merci de votre attention.