Partager via

Utilisation de fonctions personnalisées

  • Article

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 décrit 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. Ces étapes vous permettent d’accéder à 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, la sélection a été effectuée pour le premier fichier de la liste, qui se trouve être le fichier April 2019.csv.

Capture d’écran de la sélection d'un fichier qui servira d'échantillon.

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.

Capture d’écran affichant la nouvelle requête Sample File.

Créez un paramètre portant le nom Fichier paramètre et type de Binaire. Utilisez la requête Sample File comme valeur par défaut et valeur actuelle.

Capture d'écran avec les valeurs des paramètres du fichier remplies.

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.

Capture d’écran avec l’option Référence sélectionnée pour File Parameter.

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

Capture d’écran avec la requête renommée 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.

Capture d’écran de l’option Créer une fonction utilisée pour la requête Transform Sample file.

Cette opération crée une fonction qui est 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.

Capture d’écran de la fenêtre Créer une fonction pour Transform File.

Après avoir créé la fonction, vous remarquerez 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.

Capture d’écran du groupe de fonctions dans le volet Requêtes.

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 lien est ce que l'on appelle le concept d'échantillon de requête lié à une fonction.

La première transformation à apporter à cette requête est une transformation qui interprète 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.

Capture d’écran du menu déroulant binaire avec CSV mis en évidence.

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 6, comme illustré dans l’image suivante.

Capture d’écran de l’échantillon de données CSV avant le traitement.

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.

    Capture d’écran après la suppression des lignes supérieures de la requête Transform Sample file.

    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.

    Capture d’écran après l'utilisation de la première ligne comme en-tête.

Après avoir promu vos en-têtes de colonne, Power Query ajoute par défaut une nouvelle étape Changed Type qui détecte automatiquement les types de données pour chaque colonne. Votre requête Transform Sample file ressemble à 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.

Capture d’écran de l'échantillon de requête de transformation finale.

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 (CSV Files dans cet exemple). 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.

Capture d'écran avec le bouton Appeler une fonction personnalisée mis en évidence avec les paramètres de la boîte de dialogue Appeler une fonction personnalisée.

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.

Capture d’écran avec la fonction personnalisée appelée et uniquement les colonnes Nom et Table de sortie restantes.

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 sont transformées dans la forme que vous recherchez, vous pouvez développer la colonne Table de sortie en sélectionnant l’icône Développer. N’utilisez aucun préfixe pour les colonnes développées.

Capture d'écran de la boîte de dialogue développée de la table de sortie avec toutes les colonnes de la table sélectionné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.

Capture d'écran avec la liste déroulante de la date validant que la table finale contient les données de tous les fichiers.

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 avez déjà généré. 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.

Capture d’écran du nouveau paramètre avec ses valeurs requises.

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.

Capture d’écran de la boîte de dialogue des lignes de filtrage avec la colonne Filtrer le pays en utilisant le nouveau 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 deux paramètres basés sur les deux paramètres utilisés par votre Transform Sample file.

Capture d’écran de la fonction maintenant mise à jour avec deux paramètres.

Toutefois, la requête Fichiers CSV contient un symbole d’avertissement. Maintenant que votre fonction est 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.

Capture d’écran du message d’erreur affiché après une mise à jour de fonction.

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.

Capture d’écran des arguments de la fonction personnalisée appelée mise à jour.

Vous pouvez maintenant revenir à laTable de sortie développée dans les Étapes appliquées. Contrôlez 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.

Capture d’écran de la table de sortie finale après la mise à jour des arguments.

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

Capture d’écran de la liste d’origine des codes.

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.

Capture d'écran de la boîte de dialogue Gérer les paramètres avec les valeurs du code de l'exemple de paramètre saisies.

À 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

Capture d'écran de la requête de Sample Transform avec chaque partie dans sa propre colonne.

Le code M suivant illustre ce jeu de transformations.

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.

Capture d’écran de la liste des codes avec les valeurs Invoke Custom Function renseignées.

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

Capture d’écran affichant la requête de sortie finale après l’appel d’une fonction personnalisée.