Vous connaissez peut-être bien les requêtes avec paramètres et leur utilisation dans SQL ou Microsoft Query. Toutefois, les paramètres de Power Query présentent des différences essentielles :
- Les paramètres peuvent être utilisés dans n’importe quelle étape de la requête. En plus de fonctionner comme un filtre de données, les paramètres peuvent être utilisés pour spécifier des éléments tels qu’un chemin d’accès de fichier ou un nom de serveur.
- Les paramètres ne demandent pas d’entrée. Au lieu de cela, vous pouvez rapidement modifier leur valeur à l’aide de Power Query. Vous pouvez même stocker et récupérer les valeurs des cellules dans Excel.
- Les paramètres sont enregistrés dans une simple requête avec paramètres, mais sont distincts des requêtes de données dans lesquelles ils sont utilisés. Une fois créé, vous pouvez ajouter un paramètre aux requêtes selon vos besoins.
Remarque Si vous souhaitez obtenir l’autre méthode pour créer des requêtes avec paramètres, consultez Créer une requête avec paramètres dans Microsoft Query.
Créer un paramètre
Vous pouvez utiliser un paramètre pour modifier automatiquement une valeur dans une requête et éviter de modifier la requête à chaque fois pour modifier la valeur. Il vous suffit de modifier la valeur du paramètre. Une fois que vous avez créé un paramètre, celui-ci est enregistré dans une requête de paramètres spéciaux que vous pouvez modifier directement à partir d’Excel.
Sélectionner des données>,obtenir des données>, d’autres sources>,lancer l’Éditeur Power Query.
Dans l’Éditeur Power Query, sélectionnez Accueil>Gérer les paramètres > Nouveaux paramètres.
Dans la boîte de dialogue Gérer les paramètres , sélectionnez Nouveau.
Définissez les éléments suivants selon vos besoins :
Nom Cela doit refléter la fonction du paramètre, mais être aussi court que possible. Description Il peut contenir tous les détails qui aideront les utilisateurs à utiliser correctement le paramètre. Requis Effectuez l’une des opérations suivantes :
Toute valeur Vous pouvez entrer n’importe quelle valeur de n’importe quel type de données dans la requête avec paramètres.
Liste des valeurs Vous pouvez limiter les valeurs à une liste spécifique en les entrant dans la petite grille. Vous devez également sélectionner une valeur par défaut et une valeur actuelle ci-dessous.
Requête Sélectionnez une requête de liste qui ressemble à une colonne de liste structurée séparée par des virgules et entourée d’accolades.
Par exemple, un champ de status de problèmes peut comporter trois valeurs : {"Nouveau », « En cours », « Fermé"}. Vous devez créer la requête de liste au préalable en ouvrant l’Éditeur avancé (sélectionnez Accueil>, Éditeur avancé), en supprimant le modèle de code, en entrant la liste de valeurs au format de liste de requêtes, puis en sélectionnant Terminé.
Une fois la création du paramètre terminée, la requête de liste s’affiche dans vos valeurs de paramètre.Type Spécifie le type de données du paramètre. Valeurs suggérées Si vous le souhaitez, ajoutez une liste de valeurs ou spécifiez une requête pour fournir des suggestions d’entrée. Valeur par défaut S’affiche uniquement si l’option Valeurs suggérées est définie sur Liste de valeurs et spécifie l’élément de liste par défaut. Dans ce cas, vous devez choisir une valeur par défaut. Valeur actuelle Selon l’endroit où vous utilisez le paramètre, s’il est vide, la requête peut ne renvoyer aucun résultat. Si Obligatoire est sélectionné, la valeur actuelle ne peut pas être vide. Pour créer le paramètre, sélectionnez OK.
Utiliser un paramètre pour modifier une source de données
Voici un moyen de gérer les modifications apportées aux emplacements des sources de données et d’éviter les erreurs d’actualisation. Par exemple, en supposant un schéma et une source de données similaires, créez un paramètre pour modifier facilement une source de données et éviter les erreurs d’actualisation des données. Parfois, le serveur, la base de données, le dossier, le nom de fichier ou l’emplacement change. Peut-être qu’un gestionnaire de base de données remplace occasionnellement un serveur, qu’une perte mensuelle de fichiers CSV soit envoyée dans un autre dossier, ou que vous deviez facilement basculer entre un environnement de développement/test/production.
Étape 1 : créer une requête avec paramètres
Dans l’exemple suivant, vous avez plusieurs fichiers CSV que vous importez à l’aide de l’opération de dossier d’importation (Sélectionner Données>Obtenir les données>à partir de FilesÀ>partir du dossier) à partir du dossier C :\DataFilesCSV1. Mais parfois, un dossier différent est parfois utilisé comme emplacement pour déposer les fichiers, C :\DataFilesCSV2. Vous pouvez utiliser un paramètre dans une requête comme valeur de substitution pour le dossier différent.
Sélectionnez Accueil>Gérer les paramètres>Nouveau paramètre.
Entrez les informations suivantes dans la boîte de dialogue Gérer les paramètres :
Nom CSVFileDrop Description Autre emplacement de dépôt de fichier Requis Oui Type Texte Valeurs suggérées Toute valeur Valeur actuelle C :\DataFilesCSV1 Sélectionnez OK.
Étape 2 : Ajouter le paramètre à la requête de données
- Pour définir le nom du dossier en tant que paramètre, dans Paramètres de requête, sous Étape de requête, sélectionnez Source, puis Modifier les paramètres.
- Assurez-vous que l’option Chemin d’accès au fichier est définie sur Paramètre, puis sélectionnez le paramètre que vous venez de créer dans la liste déroulante.
- Sélectionnez OK.
Étape 3 : Mettre à jour la valeur du paramètre
L’emplacement du dossier vient de changer. Vous pouvez donc maintenant simplement mettre à jour la requête avec paramètres.
- Sélectionnez Connexions de données> & l’ongletRequêtes Requêtes>, cliquez avec le bouton droit sur la requête de paramètres, puis sélectionnez Modifier.
- Entrez le nouvel emplacement dans la zone Valeur actuelle , par exemple C :\DataFilesCSV2.
- Sélectionnez Accueil,>Fermer & charger.
- Pour confirmer vos résultats, ajoutez de nouvelles données à la source de données, puis actualisez la requête de données avec le paramètre mis à jour (Sélectionnerl’actualisation desdonnées> tout).
Utiliser un paramètre pour filtrer les données
Vous souhaitez parfois disposer d’un moyen simple pour modifier le filtre d’une requête afin d’obtenir des résultats différents sans modifier la requête ou effectuer des copies légèrement différentes de la même requête. Dans cet exemple, nous modifions une date pour modifier commodément un filtre de données.
Pour ouvrir une requête, localisez-en une précédemment chargée à partir de l’Éditeur Power Query, sélectionnez une cellule dans les données, puis sélectionnezModification de la requête>. Pour plus d’informations , voir Créer, charger ou modifier une requête dans Excel.
Sélectionnez la flèche de filtre dans n’importe quel en-tête de colonne pour filtrer vos données, puis sélectionnez une commande de filtre, telle que Filtres >de date/heureaprès. La boîte de dialogue Filtrer les lignes s’affiche.
Sélectionnez le bouton situé à gauche de la zone Valeur , puis effectuez l’une des opérations suivantes :
- Pour utiliser un paramètre existant, sélectionnez Paramètre, puis sélectionnez le paramètre souhaité dans la liste qui s’affiche à droite.
- Pour utiliser un nouveau paramètre, sélectionnez Nouveau paramètre, puis créez un paramètre.
Entrez la nouvelle date dans la zone Valeur actuelle , puis sélectionnez Accueil>Fermer & Charger.
Pour confirmer vos résultats, ajoutez de nouvelles données à la source de données, puis actualisez la requête de données avec le paramètre mis à jour (Sélectionnerl’actualisation desdonnées> tout). Par exemple, remplacez la valeur du filtre par une autre date pour afficher de nouveaux résultats.
Entrez la nouvelle date dans la zone Valeur actuelle .
Sélectionnez Accueil,>Fermer & charger.
Pour confirmer vos résultats, ajoutez de nouvelles données à la source de données, puis actualisez la requête de données avec le paramètre mis à jour (Sélectionnerl’actualisation desdonnées> tout).
Utiliser une valeur de cellule pour filtrer les données
Dans cet exemple, la valeur du paramètre de requête est lue à partir d’une cellule de votre classeur. Vous n’avez pas besoin de modifier la requête avec paramètres, il vous suffit de mettre à jour la valeur de la cellule. Par exemple, vous souhaitez filtrer une colonne à partir de la première lettre, mais changer facilement la valeur en une lettre de A à Z.
Dans la feuille de calcul d’un classeur dans lequel la requête à filtrer est chargée, créez un tableau Excel avec deux cellules : un en-tête et une valeur.
MyFilter G Sélectionnez une cellule dans le tableau Excel, puis sélectionnez Données>Obtenir des données>à partir du tableau/plage. L’Éditeur Power Query s’affiche.
Dans la zone Nom du volet Paramètres de la requête à droite, modifiez le nom de la requête pour qu’il soit plus significatif, tel que FilterCellValue.
Pour transmettre la valeur de la table, et non la table elle-même, cliquez avec le bouton droit sur la valeur dans Aperçu des données, puis sélectionnez Explorer.
Notez que la formule a été remplacée par= #"Changed Type"{0}[MyFilter]
Lorsque vous utilisez le tableau Excel comme filtre à l’étape 10, Power Query référence la valeur du tableau comme condition de filtre. Une référence directe au tableau Excel provoquerait une erreur.Sélectionnez Accueil>Fermer & charger>Fermer & Charger sur. Vous avez maintenant un paramètre de requête nommé « FilterCellValue » que vous utilisez à l’étape 12.
Dans la boîte de dialogue Importer des données, sélectionnez Créer uniquement une connexion, puis sélectionnez OK.
Ouvrez la requête que vous souhaitez filtrer avec la valeur de la table FilterCellValue, une requête précédemment chargée à partir de l’Éditeur Power Query, en sélectionnant une cellule dans les données, puis en sélectionnantModification de la requête>. Pour plus d’informations , voir Créer, charger ou modifier une requête dans Excel.
Sélectionnez la flèche de filtre dans n’importe quel en-tête de colonne pour filtrer vos données, puis sélectionnez une commande de filtre, telle que Les filtres> de textecommencent par. La boîte de dialogue Filtrer les lignes s’affiche.
Entrez une valeur dans la zone Valeur , par exemple « G », puis sélectionnez OK. Dans ce cas, la valeur est un espace réservé temporaire pour la valeur de la table FilterCellValue que vous entrez à l’étape suivante.
Sélectionnez la flèche sur le côté droit de la barre de formule pour afficher la formule entière. Voici un exemple de condition de filtre dans une formule :
= Table.SelectRows(#"Changed Type », each Text.StartsWith([Name], « G »))
Sélectionnez la valeur du filtre. Dans la formule, sélectionnez « G ».
À l’aide de M Intellisense, entrez les premières lettres de la table FilterCellValue que vous avez créée, puis sélectionnez-la dans la liste qui s’affiche.
Sélectionnez Accueil>Fermer>Fermer & charger.
Résultat
Votre requête utilise désormais la valeur du tableau Excel que vous avez créé pour filtrer les résultats de la requête. Pour utiliser une nouvelle valeur, modifiez le contenu des cellules du tableau Excel d’origine à l’étape 1, remplacez « G » par « V », puis actualisez la requête.
Contrôler l’utilisation des requêtes avec paramètres
Vous pouvez contrôler si les requêtes de paramètres sont autorisées ou non.
- Dans l’Éditeur Power Query, sélectionnezOptions et paramètresdes fichiers>>Options> de requête :Éditeur Power Query.
- Dans le volet de gauche, sous GLOBAL, sélectionnez l’Éditeur Power Query.
- Dans le volet de droite, sous Paramètres, sélectionnez ou décochez Toujours autoriser le paramétrage dans les boîtes de dialogue de source de données et de transformation.