Utilisation de fonctions personnalisées
Si vous vous trouvez dans une situation où vous devez appliquer le même ensemble de transformations à différentes requêtes ou valeurs, la création d’une fonction personnalisée Power Query qui peut être réutilisée autant de fois que nécessaire peut être 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 et est créé à partir de fonctions et d’opérateurs M natifs.
Vous pouvez créer manuellement votre propre fonction personnalisée Power Query à l’aide du langage de formule Power Query M , ou l’interface utilisateur Power Query vous offre des fonctionnalités pour accélérer, simplifier et améliorer le processus de création et de gestion d’une fonction personnalisée.
Tout d’abord, nous allons aborder les étapes de base pour la création d’une fonction personnalisée avec du code dans l’interface utilisateur, puis nous allons nous concentrer sur l’utilisation de l’interface pour transformer des actions complexes en une fonction réutilisable.
Important
Cet article explique comment créer une fonction personnalisée avec Power Query à l’aide de transformations courantes accessibles dans l’interface utilisateur de Power Query. Il se concentre sur les concepts de base pour créer des fonctions personnalisées et des liens vers d’autres articles de la documentation Power Query pour plus d’informations sur les transformations spécifiques référencées dans cet article.
Créer une fonction personnalisée à partir du code dans l’interface utilisateur
Note
Les étapes suivantes peuvent être suivies dans Power BI Desktop ou à l’aide de l’expérience Power Query trouvée dans Excel pour Windows.
- Utilisez l’expérience du connecteur pour vous connecter à vos données où elles sont hébergées. Lorsque vous avez sélectionné vos données, sélectionnez le bouton Transformer des données ou Modifier. Cela vous amènera à l’expérience Power Query.
- Cliquez avec le bouton droit sur un emplacement vide dans le volet Requêtes à gauche.
- Sélectionnez Requête vide.
- Dans la nouvelle fenêtre de requête vide, sélectionnez le menu Accueil, puis Éditeur avancé.
- Remplacez le modèle par votre fonction personnalisée. Par exemple:
let HelloWorld = () => ("Hello World") in HelloWorld - Sélectionnez Terminé.
Pour plus d’informations sur le développement de fonctions personnalisées avec le langage de formule Power Query M, consultez cet article : Understanding Power Query M Functions. Dans les sections suivantes, des didacticiels décrivent comment utiliser l’interface utilisateur Power Query pour développer des fonctions personnalisées sans écrire de code et des instructions sur la façon d’appeler votre fonction personnalisée dans votre requête.
Tutoriel Créer une fonction personnalisée à partir d’une référence de table
Note
L’exemple suivant a été créé à l’aide de l’expérience de bureau trouvée dans Power BI Desktop et peut également être suivi à l’aide de l’expérience Power Query trouvée dans Excel pour Windows.
Vous pouvez suivre cet exemple en téléchargeant les exemples de fichiers utilisés dans cet article à partir du lien de téléchargement suivant. Par souci de simplicité, cet article utilise le connecteur de dossiers. Pour en savoir plus sur le connecteur Dossier, accédez à Dossier. L’objectif de cet exemple est de créer une fonction personnalisée qui peut être appliquée à tous les fichiers de ce dossier avant de combiner toutes les données 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 amènent à 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é réalisée pour le premier fichier de la liste, qui est le fichier Avril 2019.csv.
Cette option crée efficacement une requête avec une étape de navigation directement vers ce fichier en tant que fichier binaire, et le nom de cette nouvelle requête est le chemin d’accès au fichier sélectionné. Renommez cette requête Sample File.
Créer un nouveau paramètre avec le nom Paramètre de fichier et de type binaire. Utilisez la requête Sample File comme Valeur par défaut et Valeur actuelle.
Note
Nous vous recommandons de lire l’article sur Parameters pour mieux comprendre comment créer et gérer des paramètres dans Power Query.
Les fonctions personnalisées peuvent être créées à l’aide de n’importe quel type de paramètres. Aucune fonction personnalisée n’est requise pour avoir un fichier binaire en tant que paramètre.
Le type de paramètre binaire s’affiche uniquement dans la boîte de dialogue Paramètres sous le menu déroulant Type lorsque vous avez une requête qui se traduit par un binaire.
Il est possible de créer une fonction personnalisée sans paramètre. Il s’agit généralement de 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, et crée une chaîne de texte spécifique à partir de ces valeurs.
Cliquez avec le bouton droit sur paramètre de fichier dans le volet Requêtes. Sélectionnez l’option Référence.
Renommez la requête nouvellement créée de Paramètre de fichier (2) en Fichier d'exemple transformé.
Cliquez avec le bouton droit sur cette nouvelle requête transformer l’exemple de fichier, puis sélectionnez l’option Créer une fonction.
Cette opération crée efficacement une nouvelle fonction qui se lie avec la requête du fichier d'exemple Transform. 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 fichier Transform comme nom de fonction .
Après avoir créé la fonction, notez qu’un nouveau groupe est créé pour vous avec le nom de votre fonction. Ce nouveau groupe contient :
- Tous les paramètres référencés dans la requête de votre fichier d'exemple Transform.
- Votre requête Transform Sample file, communément appelée exemple de requête.
- Dans ce cas, votre fonction nouvellement créée Transformer le fichier.
Application de transformations à un exemple de requête
Une fois votre nouvelle fonction créée, sélectionnez la requête avec le nom Transform Sample file. Cette requête est maintenant liée à la fonction du fichier de transformation. Les modifications apportées à cette requête sont donc reflétées dans la fonction. Cette connexion est ce qu’on appelle le concept d’un exemple de requête lié à une fonction.
La première transformation qui doit se produire à cette requête est une transformation qui interprète le binaire. Vous pouvez cliquer avec le bouton droit sur le fichier binaire dans le volet d’aperçu et sélectionner l’option CSV pour interpréter le fichier binaire en tant que 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 dans la ligne 5 et les données commencent à partir de la ligne 6 vers le bas, comme indiqué dans l’image suivante.
L’ensemble suivant d’étapes de transformation qui doivent être appliquées à l’exemple de fichier de transformation sont les suivantes :
Supprimer les quatre premières lignes: cette action se débarrasse des lignes considérées comme faisant partie de la section d’en-tête du fichier.
Note
Pour en savoir plus sur la suppression de lignes ou le filtrage d’une table par position de ligne, accédez à Filtrer par position de ligne.
Promouvoir les en-têtes: les en-têtes de votre tableau final se trouvent maintenant sur la première ligne du tableau. Vous pouvez les promouvoir comme indiqué dans l’image suivante.
Après avoir promu vos en-têtes de colonne, Power Query ajoute par défaut automatiquement une nouvelle étape Type modifié qui identifie automatiquement les types de données pour chaque colonne. Votre requête Transform Sample file ressemble à l’image suivante.
Note
Pour en savoir plus sur la promotion et la rétrogradation des en-têtes de colonne, accédez à Promouvoir ou rétrograder des en-têtes de colonne.
Attention
Votre fonction Transformer le fichier s’appuie sur les étapes effectuées dans la requête Transformer l’exemple de fichier. Toutefois, si vous essayez de modifier manuellement le code de la fonction de transformation de fichier , vous serez accueilli avec 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 où vous disposez de la liste des fichiers du dossier (fichiers CSV dans cet exemple). Dans 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 de la requête de fonction . Après avoir sélectionné la fonction dans le menu déroulant, le paramètre de la fonction s’affiche et vous pouvez sélectionner la colonne dans la table à utiliser comme argument pour cette fonction. Sélectionnez la colonne Contenu comme valeur/argument à passer pour le paramètre de fichier .
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, sauf Nom et table de sortie.
Note
Pour en savoir plus sur la façon de choisir ou de supprimer des colonnes d’une table, accédez à Choisir ou supprimer des colonnes.
Votre fonction a été appliquée à chaque ligne de la table à l’aide des valeurs de la colonne Contenu en tant qu’argument pour votre fonction. Maintenant que les données sont transformées dans la forme que vous recherchez, vous pouvez développer la colonne de la table de sortie en sélectionnant l'icône Développer. Ne pas utiliser de préfixe pour les colonnes élargies.
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 ce cas, vous pouvez vérifier les valeurs de la colonne Date, car chaque fichier contient uniquement les données d’un mois donné à partir d’une année donnée. Si vous voyez plusieurs données, cela signifie que vous avez correctement combiné des données de plusieurs fichiers dans une seule table.
Note
Ce que vous avez lu jusqu'à présent est fondamentalement le même processus que celui qui se produit pendant l'expérience de la combinaison de fichiers , mais effectué manuellement.
Nous vous recommandons également de lire l’article sur Vue d'ensemble de la combinaison de fichiers et Combiner des fichiers CSV pour mieux comprendre le fonctionnement de la fonctionnalité de combinaison de fichiers dans Power Query et le rôle des fonctions personnalisées.
Ajouter un nouveau paramètre à une fonction personnalisée existante
Imaginez qu’il existe une nouvelle exigence en plus de ce que vous avez construit actuellement. La nouvelle exigence exige qu’avant de combiner les fichiers, vous filtrez les données à l’intérieur de celles-ci pour obtenir uniquement les lignes où le pays est égal à Panama.
Pour satisfaire à cette exigence, créez un paramètre appelé Market avec le type de données texte. Pour la valeur actuelle, 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.
Note
Pour en savoir plus sur le filtrage des colonnes par valeurs, accédez à Valeurs de filtre.
L’application de cette nouvelle étape à votre requête met automatiquement à jour la fonction Transformer le fichier, ce qui nécessite maintenant deux paramètres en fonction des deux paramètres utilisés par votre exemple de fichier Transform.
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.
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 revenir à Table de sortie développée dans les Étapes appliquées. Vérifiez votre requête pour vérifier que seules les lignes où Pays est égal à 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 agit comme un élément de logique réutilisable. Plus tard, cette fonction personnalisée peut être appelée par rapport aux requêtes ou valeurs de votre choix. Cette fonction personnalisée peut vous faire gagner du temps et vous aider à gérer votre ensemble de transformations dans un emplacement central, que vous pouvez modifier à tout moment.
Par exemple, imaginez une requête qui comporte plusieurs codes sous forme de chaîne de texte et que vous souhaitez créer une fonction qui décode ces valeurs, comme dans l’exemple de tableau suivant :
| code |
|---|
| PTY-CM1090-LAX |
| LAX-CM701-PTY |
| PTY-CM4441-MIA |
| MIA-UA1257-LAX |
| LAX-XY2842-MIA |
Vous commencez par avoir un paramètre qui a une valeur qui sert d’exemple. Pour ce cas, 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 ce cas, vous souhaitez fractionner le code PTY-CM1090-LAX en plusieurs composants :
- Origine = PTY
- Destination = LAX
- Compagnie aérienne = CM
- ID du vol = 1090
Le code M suivant illustre cet ensemble 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"
Note
Pour en savoir plus sur le langage de formule Power Query M, consultez 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 indiqué dans l’image suivante.
Après quelques transformations supplémentaires, vous pouvez voir que vous avez atteint votre sortie souhaitée et appliqué la logique pour une telle transformation à partir d’une fonction personnalisée.
