Meilleures pratiques de flux de langage Power Query

Dans ce didacticiel, vous découvrirez le flux de langage Power Query et certaines de ses meilleures pratiques. Vous apprendrez également à transformer facilement vos données pour obtenir les meilleurs résultats possibles. Les transformations de données optimiseront votre rapport et le rendront attrayant.

Table des matières

• Définir les types de données sans flux de langage Power Query
  • Définir les types de données à l'aide de l'onglet Transformer
  • Définir les types de données à l'aide de l'en-tête de colonne
  • Définir les types de données à l'aide des icônes de colonne
• Définir les types de données avec le flux de langage Power Query
  • Supprimer les colonnes inutiles
  • Attribuer des types de données appropriés aux colonnes et limiter les lignes
  • Consolider les étapes redondantes dans le flux de langage Power Query
• Organiser les requêtes pour un meilleur flux de langage Power Query
• Résumé du flux de langage Power Query
• Conclusion

Définir les types de données sans flux de langage Power Query

Les transformations minimales que vous devez toujours effectuer consistent à renommer et à définir les types de données pour vos colonnes.

Les types de données sont utilisés pour classer les valeurs dans M . Une icône à côté du nom de la colonne indique le type de données affecté à un champ. Dans cet exemple de tableau, vous pouvez voir une icône ABC123 à côté des noms de colonne. Cette icône représente le type de données Any qui indique qu'un type de données n'a pas été affecté à cette colonne. 

En dehors des livres, la détection automatique du type est activée. Cela génère automatiquement des étapes de type modifié dans vos requêtes. Même si cela est activé, assurez-vous toujours que Power Query devine correctement les types de données pour chacune des colonnes de toutes vos tables.

Pour activer ou désactiver ce paramètre, procédez comme suit.

Sous l' option Global , vous pouvez sélectionner l'une des trois options de détection de type à votre disposition.

Sous l'option Fichier actuel, vous pouvez basculer les options de détection de type en fonction de votre paramètre global.

Définir les types de données à l'aide de l'onglet Transformer

L'interface utilisateur offre plusieurs façons de définir les types de données pour vos colonnes. Dans la section Toute colonne de l'onglet Transformer, vous trouverez le bouton Détecter le type de données .

Si vous sélectionnez toutes les colonnes et cliquez sur ce bouton, il définira automatiquement les types de données de toutes les colonnes. Cependant, comme il n'analyse que les 200 premières lignes, vous devez toujours vérifier si Power Query a le bon type de données.

Si vous sélectionnez une colonne dans votre tableau, vous pouvez voir son type de données dans la section Toute colonne.

Si vous cliquez sur le bouton déroulant et sélectionnez Texte, l'icône de la colonne sélectionnée passera de ABC123 à simplement ABC .

Vous verrez alors une étape Type modifié dans le volet Étapes appliquées.

Dans l'onglet Accueil, vous trouverez également la possibilité de définir le type de données.

Définir les types de données à l'aide de l'en-tête de colonne

Une autre méthode consiste à cliquer avec le bouton droit sur un en-tête et à sélectionner Modifier le type . À partir de là, vous pouvez choisir le type de données approprié pour vos colonnes.

Définir les types de données à l'aide des icônes de colonne

La manière la plus courante de définir des types de données consiste à cliquer sur l'icône en regard du nom de la colonne. Il vous montrera tous les types de données disponibles.

Dans l'exemple de table, définissez le type de données de la colonne OrderDate sur Date .

Selon le type de données de la colonne que vous sélectionnez, Power Query fournit un ensemble de transformations et d'options qui s'appliquent à ce type de données spécifique dans l' onglet Transformer , l'onglet Ajouter une colonne et la section Filtre intelligent .

Ainsi, si vous sélectionnez une colonne de date et accédez à l'onglet Transformer, vous verrez des options sous le bouton Date.

Cependant, si vous sélectionnez une colonne de texte, les options sous le bouton Date ne seront plus disponibles.

Pour le filtrage intelligent, les colonnes de texte ont des filtres de texte et les colonnes de date ont des filtres de date.

Pour la colonne suivante, remplacez le nom de la colonne par Customer Key et le type de données par Whole Number .

Ensuite, modifiez le type de données des colonnes Canal, Code de devise et Code d'entrepôt en Texte.

Ensuite, modifiez le nom de Delivery Region Index en Delivery Region Key et son type de données en Whole Number . Ensuite, faites de même pour les colonnes Product Description Index et Order Quantity .

Enfin, pour les colonnes Prix unitaire, Total de la ligne et Coût unitaire, définissez le type de données sur Nombre décimal fixe .

Définir les types de données avec le flux de langage Power Query

En raison des modifications apportées aux colonnes, de nombreuses étapes ont été créées dans le volet Étapes appliquées. Le problème est qu'en raison de l'exécution du même type de transformations plusieurs fois, l'exemple de requête de table devient inefficace. C'est quelque chose que vous devriez éviter.

Pour rendre votre requête efficace, essayez de créer une seule étape en appliquant cette transformation spécifique à toutes vos colonnes avant de créer une autre étape.

Dupliquez l'exemple de requête de table et renommez-le Best Practice . Dans le volet Étapes appliquées, cliquez avec le bouton droit sur la première transformation effectuée dans la requête précédente et sélectionnez Supprimer jusqu'à la fin . Ensuite, confirmez la suppression de l'étape dans la boîte de dialogue Supprimer l'étape .

De plus, voici quelques bonnes pratiques. La première chose à faire est de créer des paramètres pour contenir l'emplacement de la source de données. Cela facilite la résolution des problèmes lorsqu'un nom de fichier a été modifié.

Pour créer des paramètres, cliquez sur Gérer les paramètres dans l'onglet Accueil et sélectionnez Nouveau paramètre .

Une autre méthode consiste à cliquer avec le bouton droit sur le volet Requête et à sélectionner Nouveau paramètre .

Après cela, une boîte de dialogue Gérer les paramètres apparaîtra. Nommez le paramètre FileLocation et définissez le Type sur Texte. Pour les valeurs suggérées, définissez-la sur Liste de valeurs afin de pouvoir coller votre chaîne et modifier ou ajouter plusieurs emplacements entre lesquels vous pouvez basculer.

Ensuite, allez dans votre explorateur de fichiers et sélectionnez votre fichier. Copiez le chemin et collez les paramètres. Une fois terminé, appuyez sur OK .

Revenez à la requête Meilleures pratiques et cliquez sur l'étape Source dans le volet Étapes appliquées. Ensuite, modifiez le chemin d'accès au fichier codé en dur dans la barre de formule avec FileLocation .

Supprimer les colonnes inutiles

Supprimez toutes les colonnes inutiles pour économiser de l'espace et améliorer les performances. Apportez uniquement les données dont vous avez besoin, car ajouter des tables et des colonnes est beaucoup plus facile que de les supprimer.

La façon la plus simple de le faire est de passer par l' étape Choisir les colonnes dans l'onglet Accueil. Si vous cliquez sur ce bouton, une boîte de dialogue apparaîtra qui vous permettra de choisir les colonnes à conserver. Vous devez concevoir et mettre en forme vos tableaux dans un but précis pour mieux répondre à l'analyse que vous allez effectuer.

À partir de là, désélectionnez les colonnes dont vous n'avez pas besoin dans votre tableau. Pour cet exemple de table, la cardinalité de la colonne OrderNumber est élevée. Il est préférable de désélectionner cette colonne car elle a un impact sur la taille du fichier et les performances globales.

Si vous n'avez pas besoin de données de localisation pour l'analyse de l'exemple de requête, il est également préférable de désélectionner Delivery Region Index . Enfin, puisque le tableau a déjà un prix unitaire et une quantité, la colonne Total de la ligne n'est pas nécessaire.

Après avoir désélectionné les colonnes, appuyez sur OK . Si vous souhaitez modifier la sélection de colonne, effacez simplement l'icône d'engrenage à côté du nom de l'étape dans le volet Étapes appliquées.

Attribuer des types de données appropriés aux colonnes et limiter les lignes

Attribuez ensuite des types de données à toutes les colonnes. Sélectionnez toutes les colonnes et cliquez sur Détecter le type de données dans l'onglet Transformer.

Détecter le type de données détecte automatiquement les types de données des colonnes en fonction de l'analyse des 200 premières lignes. Alors, vérifiez et assurez-vous que Power Query définit les types de données corrects.

Il est recommandé de limiter le nombre de lignes. Si votre exercice financier commence le 1er juillet, vous pouvez omettre les données de juin ou définir un paramètre.

Cependant, il y a un hic si vous souhaitez pouvoir modifier une valeur de paramètre dans le service LuckyTemplates . Une fois que vous avez publié votre rapport, vos valeurs de paramètre doivent être soit de type texte, soit de type décimal.

Pour gérer cela, créez un filtre sur les données en cliquant sur le bouton déroulant dans la colonne OrderDate. Ensuite, cliquez sur Filtres de date et sélectionnez Après .

Dans la boîte de dialogue, remplacez le premier paramètre par est postérieur ou égal à , puis saisissez la date. Dans cet exemple, la date saisie était le 1er juillet 2014.

Après avoir appuyé sur OK , le filtre sera alors appliqué dans votre tableau. Ensuite, créez un paramètre et nommez-le DatesFrom . Définissez le Type sur Texte et saisissez la date dans le paramètre Valeur actuelle.

Maintenant, revenez à la requête Best Practice et remplacez le #date intrinsèque par Date.From(DatesFrom) .

Cliquer sur la coche sans la fonction Date.From renverra une erreur. En effet, DatesFrom est un type Texte alors que le champ a un type Date. La fonction Date.From convertit le texte en date.

Renommez toutes les colonnes qui ne seront pas masquées dans le modèle de données. Le nom doit être concis, auto-descriptif et convivial. Gardez à l'esprit que vous concevez un modèle de données pour les personnes qui vont utiliser votre rapport.

Consolider les étapes redondantes dans le flux de langage Power Query

La prochaine chose à faire est de consolider les étapes redondantes (telles que renommer, supprimer et modifier les types de données de colonne). De plus, des étapes telles que la réorganisation des colonnes sont des éléments auxquels vous devez faire attention lorsque vous recherchez des redondances.

Les tables que vous chargez dans le modèle de données ne sont jamais affichées dans votre rapport. Cela rend l'ordre des colonnes sans importance.

Une autre bonne pratique consiste à renommer vos étapes dans le volet Étapes appliquées. Les noms des étapes sont auto-descriptifs et sont utilisés comme variables dans le code M.

Les noms qui contiennent des espaces ou des caractères spéciaux sont écrits en utilisant la notation entre guillemets. Cela signifie que les noms sont entourés de guillemets doubles et qu'ils sont précédés du signe dièse ou dièse, ce qui rend le code M difficile à lire. Vous pouvez omettre les espaces ou placer un trait de soulignement entre eux.

L'ajout de détails de documentation supplémentaires en plaçant des commentaires dans la fenêtre de l'éditeur avancé est également une bonne pratique dans Power Query. Vous pouvez également le faire dans la description de la propriété de l'étape . Ils sont affichés sous forme d'annotations d'info-bulle lorsque vous survolez une étape avec un point d'exclamation dans le volet Étapes appliquées.

Savoir pourquoi vous avez fait un certain choix lors du développement initial est extrêmement utile lorsque vous devez revoir un fichier après un certain temps. Pour ajouter des détails de documentation, cliquez avec le bouton droit sur une étape dans le volet Étapes appliquées et sélectionnez Propriétés .

Une boîte de dialogue Propriétés de l'étape apparaîtra dans laquelle vous pourrez écrire la raison du filtrage ou de la transformation.

Organiser les requêtes pour un meilleur flux de langage Power Query

L'une des meilleures pratiques les plus courantes dans Power Query consiste à organiser vos requêtes . Créez des dossiers pour les paramètres, les fonctions, les requêtes intermédiaires et les requêtes qui seront chargées dans le modèle de données. Dans cet exemple, sélectionnez les requêtes FileLocation et DatesFrom et faites un clic droit dessus. Ensuite, sélectionnez Déplacer vers le groupe et cliquez sur Nouveau groupe .

Ensuite, ajoutez un nom aux requêtes sélectionnées et appuyez sur OK .

Après avoir regroupé vos requêtes, votre volet de requête ressemblera à ceci.

Pour toutes vos requêtes de staging, assurez-vous de désactiver le chargement en décochant Activer le chargement.

Une autre chose à discuter dans ce tutoriel est le flux de langue. Chacune des étapes du volet Étapes appliquées transforme une valeur que vous pouvez voir lorsque vous cliquez dessus.

Les données initiales sont venues et ont commencé à l'étape de navigation, et les colonnes ont été sélectionnées. Ensuite, les types de données ont été modifiés et une plage de dates a été définie. Les colonnes ont également été renommées.

Toutes les étapes renvoient une valeur de type table. Si vous ouvrez la fenêtre de l'éditeur avancé, vous verrez une expression let et une clause in . Entre eux, il y a une liste d'étapes ou de noms de variables avec des expressions qui leur sont assignées.

La requête renvoie tout ce qui suit la clause in qui fait référence à la dernière étape de votre liste de variables. Le moteur M suivra ensuite la chaîne de dépendance depuis la clause in pour éliminer tout ce qui est inutile et pour repousser les transformations à la source si possible.

Résumé du flux de langage Power Query

Si vous regardez la formule, vous pouvez voir le flux des étapes effectuées dans la requête. Vous verrez également les fonctions utilisées par l' interface utilisateur à chaque étape.

La première étape qui a été faite dans la requête était la sélection de colonne. Lorsque l'étape a été exécutée à l'aide de l'interface utilisateur, la fonction Table.SelectColumns a été appelée. Comme premier paramètre, il a pris une table qui référençait le nom de la variable de l'étape précédente. Ensuite, il a répertorié tous les noms de colonnes sélectionnés.

La deuxième étape a transformé les types de colonne en appelant la fonction Table.TransformColumnTypes . Son premier paramètre appelé la sortie de l'étape précédente. Il a ensuite répertorié un ensemble de listes de transformations.

La troisième étape définit un filtre de plage de dates à l'aide de la fonction Table.SelectRows . Il a pris une requête de type table comme premier argument. Dans cet exemple, il fait référence à la sortie de l'étape Modifier le type.

La dernière étape a renommé les colonnes à l'aide de la fonction Table.RenameColumns . La sortie de l'étape précédente a été utilisée comme premier argument. Ensuite, il a répertorié un ensemble de listes de renommage.

Toutes les fonctions appliquées via l'interface utilisateur commencent par le mot Table . Ils ont tous pris une valeur de table comme premier paramètre, puis ont transformé cette valeur.

Bien que le code semble séquentiel car chaque étape fait référence à l'étape précédente, la séquence n'est pas obligatoire. Si vous déplacez une étape, la requête s'exécutera toujours car le moteur M suit toujours la chaîne de dépendance.

Avoir l'interface utilisateur est pratique mais cela supposera toujours que vous souhaitez transformer les résultats de la transformation précédente. Dans la plupart des cas, ce sera probablement vrai et dans le cas contraire, vous devrez mettre à jour manuellement cette valeur référencée dans le code.

Types de données et connecteurs Power Query
Jeux de données LuckyTemplates : Types et conventions de dénomination
Meilleures pratiques de chargement et de transformation des données

Conclusion

La transformation des données est nécessaire pour garder vos données groupées et organisées. Cela accélère le développement des données, car vous pouvez facilement suivre les problèmes dans le flux de langage Power Query et modifier les modifications dans votre rapport.

Mélisse