Utilisation de fonctions personnalisées

Si vous vous retrouvez dans une situation où vous avez besoin d’appliquer le même ensemble de transformations à différentes requêtes ou valeurs, la création d’une fonction personnalisée Power Query réutilisable autant de fois que nécessaire peut s’avérer bénéfique. Une fonction personnalisée Power Query est un mappage d’un ensemble de valeurs d’entrée à une valeur de sortie unique. Elle se crée à partir de fonctions et d’opérateurs M natifs.

Même si vous pouvez créer manuellement votre propre fonction personnalisée Power Query à l’aide de code comme indiqué dans Présentation des fonctions Power Query M, l’interface utilisateur de Power Query vous propose des fonctionnalités pour accélérer, simplifier et améliorer le processus de création et de gestion d’une fonction personnalisée.

Cet article se concentre sur cette expérience, offerte uniquement par le biais de l’interface utilisateur de Power Query, ainsi que sur la façon d’en tirer le meilleur parti.

Important

Cet article explique comment créer une fonction personnalisée avec Power Query en utilisant des transformations courantes accessibles dans l’interface utilisateur de Power Query. Il se concentre sur les principaux concepts de la création de fonctions personnalisées et propose des liens vers d’autres articles de la documentation de Power Query pour plus d’informations sur des transformations spécifiques référencées dans cet article.

Créer une fonction personnalisée à partir d’une référence de table

Remarque

L’exemple suivant a été créé à l’aide de l’expérience utilisateur proposée par Power BI Desktop. Vous pouvez aussi le suivre avec l’expérience Power Query proposée dans Excel pour Windows.

Vous pouvez suivre cet exemple en téléchargeant les exemples de fichiers utilisés dans cet article à partir de ce lien de téléchargement. Par souci de simplicité, cet article utilise le connecteur Dossier. Pour en savoir plus sur le connecteur Dossier, accédez à Dossier. L’objectif de cet exemple est de créer une fonction personnalisée applicable à tous les fichiers contenus dans ce dossier avant de combiner toutes les données issues de tous les fichiers dans une seule table.

Commencez par utiliser l’expérience du connecteur Dossier pour accéder au dossier où se trouvent vos fichiers, puis sélectionnez Transformer les données ou Modifier. Vous accédez ainsi à l’expérience Power Query. Cliquez avec le bouton droit sur la valeur Binaire de votre choix dans le champ Contenu, puis sélectionnez l’option Ajouter comme nouvelle requête. Pour cet exemple, vous allez voir que la sélection a été effectuée pour le premier fichier de la liste, qui se trouve être le fichier April 2019.csv.

Cette option crée une requête avec une étape de navigation directement dans ce fichier en tant que binaire, et le nom de cette nouvelle requête correspond au chemin du fichier sélectionné. Renommez cette requête Sample File.

Créez un paramètre portant le nom File Parameter. Utilisez la requête Sample File en tant que Valeur courante, comme illustré dans l’image suivante.

Remarque

Nous vous recommandons de lire l’article sur les paramètres pour mieux comprendre comment créer et gérer des paramètres dans Power Query.

Vous pouvez créer des fonctions personnalisées à l’aide de n’importe quel type de paramètre. Une fonction personnalisée ne doit pas nécessairement avoir un binaire en tant que paramètre.

Le type de paramètre binaire s’affiche uniquement dans le menu déroulant Type de boîte de dialogue Paramètres quand vous avez une requête qui prend la valeur d’un binaire.

Il est possible de créer une fonction personnalisée sans paramètre. C’est généralement le cas dans les scénarios où une entrée peut être déduite à partir de l’environnement dans lequel la fonction est appelée. Par exemple, une fonction qui prend la date et l’heure actuelles de l’environnement, puis qui crée une chaîne de texte spécifique à partir de ces valeurs.

Cliquez avec le bouton droit sur File Parameter dans le volet Requêtes. Sélectionnez l’option Référence.

Remplacez le nom de la requête nouvellement créée, à savoir File Parameter (2), par Transform Sample file.

Cliquez avec le bouton droit sur cette nouvelle requête Transform Sample file, puis sélectionnez l’option Créer une fonction.

Cette opération crée une fonction qui sera liée à la requête Transform Sample file. Toutes les modifications que vous apportez à la requête Transform Sample file sont automatiquement répliquées dans votre fonction personnalisée. Lors de la création de cette nouvelle fonction, utilisez Transform file comme nom de fonction.

Une fois la fonction créée, vous pouvez remarquer qu’un nouveau groupe portant le nom de votre fonction est créé. Ce nouveau groupe contient ceci :

• Tous les paramètres qui ont été référencés dans votre requête Transform Sample file.
• Votre requête Transform Sample file, communément appelée exemple de requête.
• Votre fonction nouvellement créée, dans cet exemple, Transform file.

Application de transformations à un exemple de requête

Une fois votre fonction créée, sélectionnez la requête portant le nom Transform Sample file. Cette requête est maintenant liée à la fonction Transform file. Les modifications apportées à cette requête sont donc répercutées dans la fonction. Ce concept est celui d’un exemple de requête lié à une fonction.

La première transformation à apporter à cette requête est une transformation qui va interpréter le binaire. Vous pouvez cliquer avec le bouton droit sur le binaire dans le volet d’aperçu et sélectionner l’option CSV pour interpréter le binaire en fichier CSV.

Le format de tous les fichiers CSV dans le dossier est le même. Ils ont tous un en-tête qui s’étend sur les quatre premières lignes. Les en-têtes de colonne se trouvent à la ligne 5 et les données commencent à partir de la ligne six, comme illustré dans l’image suivante.

L’ensemble suivant d’étapes de transformation à appliquer à Transform Sample file est :

1. Supprimer les quatre premières lignes : Cette action permet de se débarrasser des lignes considérées comme faisant partie de la section d’en-tête du fichier.

   

   Remarque

   Pour en savoir plus sur la marche à suivre pour supprimer des lignes ou filtrer une table par position de ligne, consultez Filtrer par position de ligne.

2. Promouvoir les en-têtes : Les en-têtes de votre table finale se trouvent maintenant à la première ligne de la table. Vous pouvez les promouvoir comme indiqué dans l’image suivante.

   

Par défaut, Power Query ajoute automatiquement une nouvelle étape Type modifié après la promotion de vos en-têtes de colonne qui permet de détecter automatiquement les types de données de chaque colonne. Votre requête Transform Sample file va ressembler à l’image suivante.

Remarque

Pour en savoir plus sur la marche à suivre pour promouvoir et rétrograder les en-têtes, consultez Promouvoir ou rétrograder des en-têtes de colonne.

Attention

Votre fonction Transform file s’appuie sur les étapes effectuées dans la requête Transform Sample file. Toutefois, si vous essayez de modifier manuellement le code de la fonction Transform file, vous recevez un avertissement qui indique The definition of the function 'Transform file' is updated whenever query 'Transform Sample file' is updated. However, updates will stop if you directly modify function 'Transform file'.

Appeler une fonction personnalisée en tant que nouvelle colonne

Une fois la fonction personnalisée créée et toutes les étapes de transformation incorporées, vous pouvez revenir à la requête d’origine dans laquelle vous avez la liste des fichiers issus du dossier. Sous l’onglet Ajouter une colonne dans le ruban, sélectionnez Appeler une fonction personnalisée dans le groupe Général. Dans la fenêtre Appeler une fonction personnalisée, entrez Table de sortie comme nom de la nouvelle colonne. Sélectionnez le nom de votre fonction, Transform file, dans la liste déroulante Requête de fonction. Après avoir sélectionné la fonction dans le menu déroulant, son paramètre s’affiche et vous pouvez sélectionner la colonne à utiliser dans la table en tant qu’argument de cette fonction. Sélectionnez la colonne Contenu comme valeur/argument à passer pour File Parameter.

Une fois que vous avez sélectionné OK, une nouvelle colonne portant le nom Table de sortie est créée. Cette colonne contient des valeurs Table dans ses cellules, comme illustré dans l’image suivante. Par souci de simplicité, supprimez toutes les colonnes de cette table, à l’exception de Nom et Table de sortie.

Remarque

Pour en savoir plus sur le la marche à suivre pour choisir ou supprimer les colonnes d’une table, consultez Choisir ou supprimer des colonnes.

Votre fonction a été appliquée à chaque ligne de la table à l’aide des valeurs de la colonne Contenu comme argument. Maintenant que les données ont été transformées comme vous le voulez, vous pouvez développer la colonne Table de sortie, comme illustré dans l’image ci-dessous, sans utiliser de préfixe pour les colonnes développées.

Vous pouvez vérifier que vous avez des données issues de tous les fichiers du dossier en vérifiant les valeurs indiquées dans la colonne Nom ou Date. Dans cet exemple, vous pouvez vérifier les valeurs de la colonne Date, car chaque fichier contient uniquement des données pour un seul mois d’une année donnée. Si vous en voyez plusieurs, cela signifie que vous avez correctement combiné les données de plusieurs fichiers dans une seule table.

Remarque

Ce que vous avez vu jusqu’à présent correspond foncièrement au même processus que celui qui se produit pendant l’expérience Combiner les fichiers, sauf que vous avez procédé manuellement.

Nous vous recommandons de lire aussi les articles Vue d’ensemble de la combinaison de fichiers et Combiner des fichiers CSV pour mieux comprendre le fonctionnement de l’expérience Combiner les fichiers dans Power Query et le rôle que jouent les fonctions personnalisées.

Ajouter un nouveau paramètre à une fonction personnalisée existante

Imaginez qu’une nouvelle exigence vient s’ajouter à ce que vous venez de générer. Cette nouvelle exigence stipule qu’avant de combiner les fichiers, vous devez filtrer les données qu’ils contiennent pour obtenir uniquement les lignes dans lesquelles le pays correspond au Panama.

Pour que cette exigence soit satisfaite, créez un paramètre appelé Market avec le type de données texte. Dans Valeur courante, entrez la valeur Panama.

Avec ce nouveau paramètre, sélectionnez la requête Transform Sample file et filtrez le champ Pays à l’aide de la valeur du paramètre Market.

Remarque

Pour en savoir plus sur la marche à suivre pour filtrer des colonnes par valeurs, consultez Filtrer les valeurs.

L’application de cette nouvelle étape à votre requête met automatiquement à jour la fonction Transform file, qui nécessite maintenant deux paramètres basés sur les deux paramètres utilisés par votre Transform Sample file.

Toutefois, la requête Fichiers CSV contient un symbole d’avertissement. Maintenant que votre fonction a été mise à jour, elle nécessite deux paramètres. Ainsi, l’étape à laquelle vous appelez la fonction génère des valeurs d’erreur, car un seul des arguments a été passé à la fonction Transform file pendant l’étape Fonction personnalisée appelée.

Pour corriger les erreurs, double-cliquez sur Fonction personnalisée appelée dans les étapes appliquées pour ouvrir la fenêtre Appeler une fonction personnalisée. Dans le paramètre Market, entrez manuellement la valeur Panama.

Vous pouvez maintenant contrôler votre requête pour vérifier que seules les lignes pour lesquelles la valeur Pays correspond à Panama s’affichent dans le jeu de résultats final de la requête Fichiers CSV.

Créer une fonction personnalisée à partir d’un élément de logique réutilisable

Si vous avez plusieurs requêtes ou valeurs qui nécessitent le même ensemble de transformations, vous pouvez créer une fonction personnalisée qui joue le rôle d’élément de logique réutilisable. Par la suite, vous pouvez appeler cette fonction personnalisée sur les requêtes ou valeurs de votre choix. Cette fonction personnalisée vous permet de gagner du temps et de mieux gérer votre ensemble de transformations dans un emplacement central, que vous pouvez modifier à tout moment.

Par exemple, imaginez qu’une requête contient plusieurs codes sous forme de chaîne de texte et que vous voulez créer une fonction qui va décoder ces valeurs, comme dans l’exemple de table suivant :

code
PTY-CM1090-LAX
LAX-CM701-PTY
PTY-CM4441-MIA
MIA-UA1257-LAX
LAX-XY2842-MIA

Vous commencez avec un paramètre qui a une valeur qui sert d’exemple. Dans cet exemple, il s’agit de la valeur PTY-CM1090-LAX.

À partir de ce paramètre, vous créez une requête dans laquelle vous appliquez les transformations dont vous avez besoin. Dans cet exemple, vous voulez fractionner le code PTY-CM1090-LAX en plusieurs composants :

• Origine = PTY
• Destination = LAX
• Compagnie aérienne = CM
• ID du vol = 1090

Le code M pour cet ensemble de transformations est indiqué ci-dessous.

let
    Source = code,
    SplitValues = Text.Split( Source, "-"),
    CreateRow = [Origin= SplitValues{0}, Destination= SplitValues{2}, Airline=Text.Start( SplitValues{1},2), FlightID= Text.End( SplitValues{1}, Text.Length( SplitValues{1} ) - 2) ],
    RowToTable = Table.FromRecords( {  CreateRow } ),
    #"Changed Type" = Table.TransformColumnTypes(RowToTable,{{"Origin", type text}, {"Destination", type text}, {"Airline", type text}, {"FlightID", type text}})
in
    #"Changed Type"

Remarque

Pour en savoir plus sur le langage de formule Power Query M, veuillez consulter Langage de formule Power Query M.

Vous pouvez ensuite transformer cette requête en fonction en cliquant avec le bouton droit sur la requête et en sélectionnant Créer une fonction. Enfin, vous pouvez appeler votre fonction personnalisée dans l’une de vos requêtes ou valeurs, comme illustré dans l’image suivante.

Après quelques transformations supplémentaires, vous pouvez voir que vous avez atteint la sortie escomptée et exploité la logique pour une telle transformation à partir d’une fonction personnalisée.