Meilleures pratiques lors de l’utilisation de Power Query

Ces Power Query meilleures pratiques vous aident à améliorer les performances des requêtes, à tirer parti du pliage des requêtes, à sélectionner des types de données corrects, à organiser les transformations et à réutiliser la logique avec des paramètres et des fonctions personnalisées. Ils s’appliquent à la fois aux expériences Power Query Desktop et Power Query Online.

Choisir le connecteur approprié

Power Query offre de nombreux connecteurs de données. Ces connecteurs vont de sources de données telles que TXT, CSV et Excel fichiers, à des bases de données telles que Microsoft SQL Server et des produits SaaS (Software as a Service) populaires tels que Microsoft Dynamics 365 et Salesforce. Si un connecteur conçu à usage unique n’est pas disponible dans la fenêtre Obtenir des données , utilisez un connecteur générique tel qu’ODBC ou OLE DB.

Choisissez le connecteur conçu pour votre source de données lorsqu’un connecteur est disponible. Par exemple, le connecteur SQL Server offre une meilleure expérience Obtenir des données que le connecteur ODBC générique lors de la connexion à une base de données SQL Server. Le connecteur SQL Server prend également en charge les fonctionnalités de performances telles que le pliage des requêtes. Pour en savoir plus, accédez à Vue d’ensemble de l’évaluation des requêtes et du pliage des requêtes dans Power Query.

Chaque connecteur de données suit une expérience standard, comme expliqué dans l’obtention de données. Cette expérience standardisée a une phase appelée Data Preview. Dans cette étape, vous disposez d’une fenêtre conviviale pour sélectionner les données que vous souhaitez obtenir à partir de votre source de données, si le connecteur l’autorise et un aperçu simple de ces données. Vous pouvez même sélectionner plusieurs jeux de données à partir de votre source de données via la fenêtre navigateur .

Remarque

Pour afficher la liste complète des connecteurs disponibles dans Power Query, accédez aux connecteurs dans Power Query.

Filtrer les données tôt pour améliorer les performances

Filtrez les données le plus tôt possible afin de réduire le nombre de lignes que Power Query doit traiter lors des transformations ultérieures. Pour les connecteurs qui prennent en charge le pliage des requêtes, Power Query pouvez renvoyer des filtres à la source de données, comme décrit dans Vue d’ensemble de l’évaluation des requêtes et du pliage des requêtes dans Power Query. Le filtrage des données non pertinentes limite également les données affichées dans l’aperçu des données.

Utilisez le menu filtre automatique, qui affiche une liste distincte des valeurs trouvées dans votre colonne, pour sélectionner les valeurs que vous souhaitez conserver ou filtrer. Utilisez la barre de recherche pour vous aider à trouver les valeurs dans votre colonne.

Vous pouvez également tirer parti des filtres spécifiques au type tels que Précédent pour une colonne de date, date-heure ou même de fuseau horaire.

Ces filtres spécifiques au type peuvent vous aider à créer un filtre dynamique qui récupère toujours les données qui se trouvent dans le nombre x précédent de secondes, minutes, heures, jours, semaines, mois, trimestres ou années.

Remarque

Pour en savoir plus sur le filtrage de vos données en fonction des valeurs d’une colonne, accédez à Filtrer par valeurs.

Effectuez les opérations coûteuses en dernier afin d’améliorer les performances

Pour améliorer les performances d’aperçu dans l’éditeur Power Query, effectuez des opérations coûteuses en dernier. Certaines opérations nécessitent la lecture de la source de données complète pour retourner tous les résultats et sont donc lentes à afficher un aperçu. Par exemple, si vous effectuez un tri, il est possible que les premières lignes triées soient à la fin des données sources. Pour retourner les résultats, l’opération de tri doit d’abord lire toutes les lignes.

D’autres opérations (telles que des filtres) n’ont pas besoin de lire toutes les données avant de retourner des résultats. Au lieu de cela, ils opèrent sur les données de la manière appelée « streaming ». Les données défilent, et les résultats sont renvoyés en cours de route. Dans l’éditeur Power Query, ces opérations doivent uniquement lire suffisamment de données sources pour remplir la préversion.

Dans la mesure du possible, effectuez d’abord ces opérations de diffusion en continu et effectuez des opérations plus coûteuses en dernier. L’exécution d’opérations dans cet ordre permet de réduire le temps passé à attendre le rendu de l’aperçu chaque fois que vous ajoutez une nouvelle étape à votre requête.

Utiliser un sous-ensemble de données lors du développement d’une requête

Si l’ajout de nouvelles étapes dans l’éditeur Power Query est lent, utilisez Conserver les premières lignes pour limiter les données traitées pendant que vous développez la requête. Après avoir ajouté toutes les étapes requises, supprimez l’étape Conserver les premières lignes afin que la requête terminée traite le jeu de données complet.

Utiliser les types de données corrects

Définissez le type de données correct pour chaque colonne afin que Power Query puisse rendre disponibles des transformations et des filtres spécifiques au type. Par exemple, lorsque vous sélectionnez une colonne de date, vous pouvez utiliser les options sous le groupe de colonnes Date et heure dans le menu Ajouter une colonne . Si la colonne n’a pas de jeu de types de données, ces options sont grisées.

Une situation similaire se produit pour les filtres spécifiques au type, car ils sont spécifiques à certains types de données. Si votre colonne n’a pas le type de données correct défini, ces filtres spécifiques au type ne sont pas disponibles.

Il est essentiel que vous travailliez toujours avec les types de données appropriés pour vos colonnes. Lorsque vous travaillez avec des sources de données structurées telles que des bases de données, les informations de type de données sont fournies à partir du schéma de table trouvé dans la base de données. Toutefois, pour les sources de données non structurées telles que les fichiers TXT et CSV, il est important de définir les types de données appropriés pour les colonnes provenant de cette source de données. Par défaut, Power Query offre une détection automatique des types de données pour les sources de données non structurées. Vous pouvez en savoir plus sur cette fonctionnalité et comment elle peut vous aider dans les types de données.

Remarque

Pour en savoir plus sur l’importance des types de données et sur leur utilisation, accédez aux types de données.

Profiler et explorer vos données

Avant de préparer vos données et d’ajouter des étapes de transformation, activez les outils de profilage des données Power Query pour découvrir des informations sur vos données.

Power Query fournit trois outils de profilage des données :

Tool	Ce qu’il montre
Qualité des colonnes	Proportion de valeurs dans une colonne valide, contenant des erreurs ou vides.
Distribution de colonnes	Fréquence et distribution des valeurs dans chaque colonne.
Profil de colonne	Statistiques détaillées sur une colonne sélectionnée.

Vous pouvez également interagir avec ces fonctionnalités, ce qui vous aide à préparer vos données.

Remarque

Pour en savoir plus sur les outils de profilage des données, accédez aux outils de profilage des données.

Documenter votre travail

Documentez une solution Power Query en fournissant des étapes, des requêtes et des groupes de noms et de descriptions explicites. Ces détails facilitent la compréhension et la maintenance de chaque transformation.

Bien que Power Query crée automatiquement un nom d’étape pour vous dans le volet Étapes appliquées, vous pouvez également renommer vos étapes ou ajouter une description à l’un d’entre eux.

Remarque

Pour en savoir plus sur toutes les fonctionnalités et composants disponibles figurant dans le volet étapes appliquées, accédez à l’aide de la liste des étapes appliquées.

Fractionner des requêtes volumineuses en modules

Fractionnez une requête Power Query volumineuse en requêtes référencées plus petites pour faciliter la compréhension et la maintenance de ses phases de transformation. Bien qu’une requête unique puisse contenir toutes les transformations et calculs dont vous avez besoin, une requête avec de nombreuses étapes est plus facile à gérer lorsqu’une requête fait référence à la suivante.

Par exemple, la requête suivante comporte neuf étapes et comprend une étape Fusionner avec la table Prix.

Vous pouvez fractionner cette requête en deux à l’étape Fusionner avec la table Prix . De cette façon, il est plus facile de comprendre les étapes qui ont été appliquées à la requête de vente avant la fusion. Pour effectuer cette opération, cliquez avec le bouton droit sur l’étape Fusionner avec la table Prix , puis sélectionnez l’option Extraire l’option Précédente .

Vous êtes alors invité à nommer votre nouvelle requête dans une boîte de dialogue. Cette étape fractionne efficacement votre requête en deux requêtes. Une requête comporte toutes les étapes avant la fusion. L’autre requête comporte une étape initiale qui fait référence à votre nouvelle requête, suivie des différentes étapes que vous aviez dans votre requête d’origine à partir de l’étape Merge with Prices table et au-delà.

Vous pouvez également utiliser la référence aux requêtes comme bon vous semble. Mais il est judicieux de garder vos requêtes à un niveau qui ne semble pas intimidante à première vue avec tant d’étapes.

Remarque

Pour en savoir plus sur le référencement des requêtes, accédez au volet Présentation du volet Requêtes.

Organiser des requêtes en groupes

Utilisez des groupes dans le volet des requêtes pour organiser votre travail.

Le seul objectif des groupes est de vous aider à maintenir votre travail organisé en servant de dossiers pour vos requêtes. Vous pouvez créer des groupes au sein de groupes si vous avez besoin de le faire. Le déplacement de requêtes entre groupes est aussi simple que glisser-déposer.

Essayez de donner à vos groupes un nom explicite qui vous convient et votre cas.

Remarque

Pour en savoir plus sur toutes les fonctionnalités et composants disponibles figurant dans le volet requêtes, accédez à Présentation du volet requêtes.

Requêtes à la preuve future

Concevoir des requêtes pour gérer les modifications attendues dans les données sources afin que les actualisations futures continuent de réussir. Power Query fournit des transformations qui rendent une requête résiliente lorsque les lignes, colonnes ou valeurs d’une source de données changent.

Définissez l’étendue de votre requête, notamment ce qu’elle doit faire et ce qu’elle doit prendre en compte en termes de structure, de disposition, de noms de colonnes, de types de données et de tout autre composant pertinent.

Les transformations suivantes peuvent aider une requête à rester résiliente aux modifications :

Scénario de données sources	Power Query de transformation	En savoir plus
Le nombre de lignes de données change, mais vous devez supprimer un nombre fixe de lignes de pied de page.	Supprimer les lignes inférieures	Filtrer une table par position de ligne
Le nombre de colonnes change, mais la requête a uniquement besoin de colonnes spécifiques.	Choisir des colonnes	Choisir ou supprimer des colonnes
Le nombre de colonnes change, mais la requête doit dissocier un sous-ensemble spécifique uniquement.	Transposer uniquement les colonnes sélectionnées	Dé-pivoter les colonnes
Une conversion de type de données génère des erreurs pour les valeurs qui ne sont pas conformes au type cible.	Supprimez les lignes qui contiennent des erreurs.	Traitement des erreurs

Utiliser les paramètres

Utilisez Power Query paramètres pour stocker et gérer les valeurs que vous pouvez réutiliser dans les transformations, les fonctions de source de données et les fonctions personnalisées. Les paramètres facilitent la mise à jour des requêtes, car vous pouvez modifier une valeur dans un emplacement au lieu de modifier chaque requête qui l’utilise. Deux scénarios courants sont les suivants :

• Argument d’étape : utilisez un paramètre comme argument de plusieurs transformations pilotées à partir de l’interface utilisateur.

  

• Argument fonction personnalisée : créez une fonction à partir d’une requête et référencez des paramètres en tant qu’arguments de votre fonction personnalisée.

  

Les principaux avantages de la création et de l’utilisation des paramètres sont les suivants :

• Vue centralisée de tous vos paramètres via la fenêtre Gérer les paramètres .

  

• Réutilisation du paramètre dans plusieurs étapes ou requêtes.

• Rend la création de fonctions personnalisées simples et faciles.

Vous pouvez même utiliser des paramètres dans certains des arguments des connecteurs de données. Par exemple, vous pouvez créer un paramètre pour le nom de votre serveur lors de la connexion à votre base de données SQL Server. Vous pouvez ensuite utiliser ce paramètre dans la boîte de dialogue de base de données SQL Server.

Si vous modifiez l’emplacement de votre serveur, il vous suffit de mettre à jour le paramètre du nom de votre serveur et vos requêtes sont mises à jour.

Remarque

Pour en savoir plus sur la création et l’utilisation de paramètres, accédez à Using parameters.

Créer des fonctions réutilisables

Créez une fonction Power Query personnalisée lorsque vous devez appliquer le même ensemble de transformations à différentes requêtes ou valeurs. Une fonction Power Query personnalisée mappe un ensemble de valeurs d’entrée à une valeur de sortie unique et est créée à partir des fonctions et opérateurs natifs de langage de formule M Power Query.

Par exemple, supposons que vous avez plusieurs requêtes ou valeurs qui nécessitent le même ensemble de transformations. Vous pouvez créer une fonction personnalisée que vous appelez ultérieurement par rapport aux requêtes ou valeurs de votre choix. Cette fonction personnalisée vous permet de gagner du temps et de gérer votre ensemble de transformations dans un emplacement central, que vous pouvez modifier à tout moment.

Les fonctions personnalisées Power Query peuvent être créées à partir de requêtes et de paramètres existants. 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.

Vous commencez par avoir un paramètre avec une valeur qui sert d’exemple.

À 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

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.

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.

Remarque

Pour en savoir plus sur la création et l’utilisation de fonctions personnalisées dans Power Query, consultez Fonctions personnalisées.