Vue d’ensemble de l’évaluation des requêtes et du pliage des requêtes dans Power Query

Cet article fournit une vue d’ensemble de base de la façon dont les requêtes M sont traitées et transformées en requêtes de source de données.

Script M Power Query

Toute requête, créée par Power Query, écrite manuellement par vous dans l’éditeur avancé ou entrée à l’aide d’un document vide, se compose de fonctions et de syntaxes à partir du langage de formule Power Query M. Cette requête est interprétée et évaluée par le moteur Power Query pour générer ses résultats. Le script M sert d’ensemble d’instructions nécessaires pour évaluer la requête.

Conseil / Astuce

Vous pouvez considérer le script M comme une recette qui décrit comment préparer vos données.

La façon la plus courante de créer un script M consiste à utiliser l’éditeur Power Query. Par exemple, lorsque vous vous connectez à une source de données, telle qu’une base de données SQL Server, notez sur le côté droit de votre écran qu’il existe une section appelée étapes appliquées. Cette section affiche toutes les étapes ou transformations utilisées dans votre requête. Dans ce sens, l’éditeur Power Query sert d’interface pour vous aider à créer le script M approprié pour les transformations que vous suivez et garantit que le code que vous utilisez est valide.

Remarque

Le script M est utilisé dans l’éditeur Power Query pour :

• Affichez la requête sous la forme d’une série d’étapes et autorisez la création ou la modification de nouvelles étapes.
• Afficher une vue de diagramme.

L’image précédente met l’accent sur la section étapes appliquées, qui contient les étapes suivantes :

• Source : établit la connexion à la source de données. Dans ce cas, il s’agit d’une connexion à une base de données SQL Server.
• Navigation : accède à une table spécifique dans la base de données.
• Suppression d’autres colonnes : sélectionne les colonnes de la table à conserver.
• Lignes triées : trie la table à l’aide d’une ou plusieurs colonnes.
• Lignes supérieures conservées : filtre le tableau pour ne conserver que certaines lignes du haut du tableau.

Cet ensemble de noms d’étapes est un moyen convivial d’afficher le script M que Power Query a créé pour vous. Il existe plusieurs façons d’afficher le script M complet. Dans Power Query, vous pouvez sélectionner Éditeur avancé sous l’onglet Affichage . Vous pouvez également sélectionner l’Éditeur avancé dans le groupe de requêtes sous l’onglet Accueil . Dans certaines versions de Power Query, vous pouvez également modifier l’affichage de la barre de formule pour afficher le script de requête en accédant à l’onglet Affichage et à partir du groupe Disposition, sélectionnez Script d’affichage>Script de requête.

La plupart des noms trouvés dans le volet Étapes appliquées sont également utilisés comme c’est le cas dans le script M. Les étapes d’une requête sont nommées à l’aide d’identificateurs dans la langue M. Parfois, des caractères supplémentaires sont encapsulés autour des noms d’étapes dans M, mais ces caractères ne sont pas affichés dans les étapes appliquées. Un exemple est #"Kept top rows", qui est classé comme identificateur entre guillemets en raison de ces caractères supplémentaires. Un identificateur entre guillemets peut être utilisé pour permettre à n’importe quelle séquence de zéro ou plusieurs caractères Unicode d’être utilisés comme identificateur, y compris les mots clés, les espaces blancs, les commentaires, les opérateurs et les ponctuateurs. Pour en savoir plus sur les identificateurs dans la langue M, accédez à la structure lexicale.

Toutes les modifications apportées à votre requête via l’éditeur Power Query mettent automatiquement à jour le script M de votre requête. Par exemple, en utilisant l’image précédente comme point de départ, si vous modifiez le nom de l’étape Lignes du haut conservées en 20 premières lignes, cette modification est automatiquement mise à jour dans le mode Script.

Bien que nous vous recommandons d’utiliser l’éditeur Power Query pour créer tout ou la plupart du script M pour vous, vous pouvez ajouter ou modifier manuellement des éléments de votre script M. Pour en savoir plus sur la langue M, accédez au site officiel de la langue M.

Remarque

Le script M, également appelé code M, est un terme utilisé pour tout code qui utilise le langage Power Query M. Dans le contexte de cet article, le script M fait également référence au code trouvé à l’intérieur d’une requête Power Query et accessible via la fenêtre d’éditeur avancée ou par le biais de la vue de script dans la barre de formule.

Évaluation des requêtes dans Power Query

Le diagramme suivant explore le processus qui se produit lorsqu’une requête est évaluée dans Power Query.

1. Le script M, trouvé dans l’éditeur avancé, est soumis au moteur Power Query. D’autres informations importantes sont également incluses, telles que les informations d’identification et les niveaux de confidentialité de la source de données.
2. Power Query détermine les données à extraire de la source de données et envoie une demande à la source de données.
3. La source de données répond à la demande de Power Query en transférant les données demandées vers Power Query.
4. Power Query reçoit les données entrantes de la source de données et effectue toutes les transformations à l’aide du moteur Power Query si nécessaire.
5. Les résultats dérivés du point précédent sont chargés vers une destination.

Remarque

Bien que cet exemple présente une requête avec une base de données SQL comme source de données, le concept s’applique aux requêtes avec ou sans source de données.

Lorsque Power Query lit votre script M, il exécute le script via un processus d’optimisation pour évaluer plus efficacement votre requête. Dans ce processus, il détermine quelles étapes (transformations) de votre requête peuvent être déchargées vers votre source de données. Il détermine également quelles autres étapes doivent être évaluées à l’aide du moteur Power Query. Ce processus d’optimisation est appelé pliage des requêtes, où Power Query tente d’envoyer une grande partie de l’exécution possible à la source de données pour optimiser l’exécution de votre requête.

Important

Toutes les règles du langage de formule Power Query M (également appelée langue M) sont suivies. Plus particulièrement, l’évaluation différée joue un rôle important pendant le processus d’optimisation. Dans ce processus, Power Query comprend quelles transformations spécifiques de votre requête doivent être évaluées. Power Query comprend également les autres transformations qui n’ont pas besoin d’être évaluées, car elles ne sont pas nécessaires dans la sortie de votre requête.

En outre, lorsque plusieurs sources sont impliquées, le niveau de confidentialité des données de chaque source de données est pris en compte lors de l’évaluation de la requête. Plus d’informations : dans les coulisses du pare-feu de confidentialité des données

Le diagramme suivant illustre les étapes qui se produisent dans ce processus d’optimisation.

1. Le script M, trouvé dans l’éditeur avancé, est soumis au moteur Power Query. D’autres informations importantes sont également fournies, telles que les informations d’identification et les niveaux de confidentialité de la source de données.
2. Le mécanisme de pliage de requête envoie des demandes de métadonnées à la source de données pour déterminer les fonctionnalités de la source de données, des schémas de table, des relations entre différentes tables à la source de données, etc.
3. En fonction des métadonnées reçues, le mécanisme de repli de requête détermine quelles informations extraire de la source de données et quel ensemble de transformations doivent se produire à l’intérieur du moteur Power Query. Il envoie les instructions à deux autres composants qui s’occupent de récupérer les données à partir de la source de données et de transformer les données entrantes dans le moteur Power Query si nécessaire.
4. Une fois que les composants internes de Power Query reçoivent les instructions, Power Query envoie une demande à la source de données à l’aide d’une requête de source de données.
5. La source de données reçoit la requête de Power Query et transfère les données au moteur Power Query.
6. Une fois que les données se trouvent à l’intérieur de Power Query, le moteur de transformation (également appelé moteur mashup) effectue les transformations qui n’ont pas pu être reportées ou externalisées dans la source de données.
7. Les résultats dérivés du point précédent sont chargés vers une destination.

Remarque

Selon les transformations et la source de données utilisées dans le script M, Power Query détermine s’il diffuse ou met en mémoire tampon les données entrantes.

Vue d’ensemble du pliage des requêtes

L’objectif du pliage des requêtes est de décharger ou d’envoyer (push) autant de l’évaluation d’une requête vers une source de données qui peut calculer les transformations de votre requête.

Le mécanisme de repli de requête accomplit cet objectif en traduisant votre script M en langage que votre source de données peut interpréter et exécuter. Il envoie ensuite l’évaluation à votre source de données et envoie le résultat de cette évaluation à Power Query.

Cette opération fournit souvent une exécution de requête plus rapide que l’extraction de toutes les données requises à partir de votre source de données et l’exécution de toutes les transformations requises dans le moteur Power Query.

Lorsque vous utilisez l’expérience obtenir des données, Power Query vous guide tout au long du processus qui vous permet de vous connecter à votre source de données. Dans ce cas, Power Query utilise une série de fonctions dans le langage M classés comme accédant aux fonctions de données. Ces fonctions spécifiques utilisent des mécanismes et des protocoles pour se connecter à votre source de données à l’aide d’un langage que votre source de données peut comprendre.

Toutefois, les étapes qui suivent dans votre requête sont les étapes ou transformations que le mécanisme de pliage de requête tente d’optimiser. Il vérifie ensuite s’ils peuvent être déchargés vers votre source de données au lieu d’être traités à l’aide du moteur Power Query.

Important

Toutes les fonctions de source de données, couramment affichées comme l’étape Source d’une requête, interrogent les données à la source de données dans son langage natif. Le mécanisme de repli de requête est utilisé sur toutes les transformations appliquées à votre requête après votre fonction de source de données. Ils peuvent ensuite être traduits et combinés en une seule requête de source de données ou autant de transformations qui peuvent être déchargées vers la source de données.

Selon la façon dont la requête est structurée, il peut y avoir trois résultats possibles pour le mécanisme de pliage de requête :

• Traitement complet des requêtes : lorsque toutes vos transformations de requête sont renvoyées à la source de données et qu’un traitement minimal se produit sur le moteur Power Query.
• Repli de requête partielle : lorsque seules quelques transformations dans votre requête, et pas toutes, peuvent être renvoyées à la source de données. Dans ce cas, seul un sous-ensemble de vos transformations est effectué à votre source de données et le reste de vos transformations de requête se produisent dans le moteur Power Query.
• Aucun pliage de requête : lorsque la requête contient des transformations qui ne peuvent pas être traduites dans le langage de requête natif de votre source de données, soit parce que les transformations ne sont pas prises en charge ou que le connecteur ne prend pas en charge le pliage des requêtes. Dans ce cas, Power Query obtient les données brutes de votre source de données et utilise le moteur Power Query pour obtenir la sortie souhaitée en traitant les transformations requises au niveau du moteur Power Query.

Remarque

Le mécanisme de pliage des requêtes est principalement disponible dans les connecteurs pour les sources de données structurées telles que, mais pas uniquement, Microsoft SQL Server et OData Feed. Pendant la phase d’optimisation, le moteur peut parfois réorganiser les étapes de la requête.

L’utilisation d’une source de données qui a plus de ressources de traitement et dispose de fonctionnalités de repli de requête peut accélérer les temps de chargement de vos requêtes à mesure que le traitement se produit à la source de données et non au moteur Power Query.

Contenu connexe

Pour obtenir des exemples détaillés des trois résultats possibles du mécanisme de pliage de requête, accédez aux exemples de pliage des requêtes.

Pour plus d’informations sur les indicateurs de repli de requête trouvés dans le volet Étapes appliquées , accédez aux indicateurs de pliage des requêtes