Sommaire
  • Vous débutez sur Power Query et vous appréhendez la prise en main de ce nouvel outil ?
  • Vous avez déjà effectué quelques requêtes et souhaitez connaître les bonnes pratiques de développement ?
  • Vous souhaitez transformer un fichier de travail personnel en véritable outil à partager ?

Dans cet article, nous partageons 5 conseils aux débutants sur Power Query.

Les 4 premiers conseils sont relatifs à la prise en main de l’outil. Le 5ème conseil est une astuce technique pour variabiliser les sources des requêtes depuis Excel !

Qu’est-ce que Power Query ?

Power Query est un outil ETL (Extract, Transform & Load) développé par Microsoft et disponible sur Excel et Power BI. Cet outil permet de :

  • Se connecter aux sources de données (= Extract) : fichiers Excel, CSV, textes, PDF, dossiers et multiples autres sources locales et cloud
  • Nettoyer et transformer les données (= Transform) : gestion des erreurs, suppressions de lignes ou de colonnes, combinaison et fusion des tables etc.
  • Charger les données (= Load) : les résultats des requêtes peuvent être chargés sous forme de tables (Excel et Power BI) ou de TCD (Excel)

Le langage utilisé dans Power Query est le « M » (pour « mash-up/mixer »). Cependant, il n’est pas nécessaire de maîtriser ce langage pour commencer à utiliser Power Query. L’utilisation de l’outil est facilitée par :

  • L’accessibilité de Power Query directement depuis Excel et Power BI (pas besoin de télécharger un add-in ou un logiciel complémentaire). Power Query est disponible dans Excel à partir de l’onglet « Données » puis « Obtenir des données »
  • Une interface rassurante qui reprend les codes d’Excel avec une barre de formule et un bandeau supérieur divisé en plusieurs intercalaires
  • Les nombreuses transformations qui peuvent s’effectuer en 1 clic (sans aucun code) : sélectionner les colonnes à conserver, supprimer des lignes, remplacer des valeurs etc.

5 astuces sur Power Query

Forts de notre expérience sur Power Query dans Excel et Power BI, nous sommes heureux de vous partager 5 astuces pour progresser sur cet outil !

Organiser ses requêtes

Pour naviguer facilement entre les requêtes et faciliter la compréhension du fichier, vous pouvez organiser vos requêtes par dossier. Dans le bandeau de gauche, vous pouvez développer une arborescence de dossiers via : clic droit > déplacer vers le groupe > nouveau groupe.

Exemple : un dossier pour les requêtes de paramètres, un dossier pour les requêtes de mapping etc.

Renommer les requêtes et les étapes

Dans la même veine, nous vous conseillons de renommer les requêtes en fonction de la structure des données (table, liste, paramètre, etc.) et de renommer les étapes en fonction de la transformation effectuée.

Exemple : renommer l’étape #« Lignes filtrées » par « Select_Pays_Paris » car les noms d’étape générés automatiquement par Power Query ne sont pas explicites.

👉 Pour aller plus loin dans la logique d’audit et de transmission du fichier, il est possible d’ajouter des commentaires au code depuis l’interface de l’éditeur avancé. Le commentaire doit être précédé d’un double slash « // » ou débuter par « /* » et terminer par « */ ». Le commentaire doit être placé au-dessus de la ligne qu’il explique.

L’ajout d’un commentaire se traduit par l’apparition d’une icône d’information dans la liste des étapes (panneau « Paramètres des requêtes »). En passant sur l’icône, le commentaire apparaît.

Afficher la barre de formule

Lorsque vous effectuez des transformations, nous vous conseillons d’afficher la barre de formule. Elle permet de vous habituer petit à petit à la syntaxe du langage M (le langage de Power Query).

Ce faisant, il devient progressivement plus facile d’effectuer des modifications directement dans le code, ou d’optimiser le code en fusionnant des étapes.

Exemple : il est possible de fusionner en une seule étape l’ajout d’une colonne et le typage des données en ajoutant un paramètre supplémentaire dans la fonction Table.AddColumn.

Afficher l’icône d’analyse des données de la colonne

Dans l’onglet Affichage, cocher la case « Qualité de la colonne » permet d’obtenir des informations précises sur les données de la colonne. Les pourcentages de données en erreur ou de valeurs vides sont en particulier affichés. Ces informations complètent l’information visuelle donnée par la barre d’erreur affichée sous l’en-tête de colonne.

Variabiliser un chemin de dossier ou de fichier depuis Excel dans Power Query

Cas d’usage :

  • vous souhaitez modifier fréquemment la source de vos données sans avoir à ouvrir Power Query
  • vous développez un outil à destination d’utilisateurs qui ne connaissent pas Power Query

Pour récupérer un chemin d’accès dans Power Query depuis Excel, suivez les étapes ci-dessous :

1 – Renseigner le chemin du fichier/dossier dans une cellule du fichier (ici B4).

2 – Nommer la cellule

Nommer la cellule en écrasant le nom « B4 » dans la zone blanche au-dessus de la colonne A. Utiliser un nom explicite, par exemple « chemin_fichier_BG_test ». Ne pas oublier de faire « entrée » après avoir saisi le nom pour que celui-ci soit validé.

3 – Créer la connexion à Power Query

Se placer sur la cellule B4. Dans « Données », cliquer sur « A partir d’un Tableau ou d’une Plage » (ou faire clic droit sur la cellule et la même option apparaît). La fenêtre Power Query s’ouvre.

4 – Retraitements dans Power Query

Dans la requête qui s’ouvre dans Power Query, supprimer les étapes autres que « Source » (qui ont été ajoutées automatiquement) en cliquant sur la croix à gauche du nom de l’étape.

Dans la table, cliquer sur la cellule qui contient le chemin d’accès. Faire un clic droit et sélectionner « Drill-down ». Cette fonction extrait le contenu de la cellule. La requête devient alors un texte.

Cette requête peut être utilisée :

  • dans une requête vide (cf. option 1) pour appeler le fichier avec la fonction
    • = File.Contents(chemin_fichier_BG_test) pour un fichier
    • = Folder.Files(chemin_fichier_BG_test) ou Folder.Contents(chemin_fichier_BG_test) pour un dossier
  • pour remplacer le chemin d’accès du fichier dans une requête déjà existante (cf. option 2)

Désormais, pour changer le chemin du fichier (afin d’appliquer les transformations sur un autre fichier du même format ou si le fichier lui-même a été déplacé dans un autre dossier), il suffit de :

– modifier le chemin d’accès dans la cellule Excel
– actualiser le résultat des requêtes concernées directement depuis Excel (onglet Données > Requêtes et connexions > dans le bandeau qui s’ouvre à droite, faire un clic droit sur la requête > Actualiser).