Power Query Excel : nettoyer et transformer tes données

C'est quoi Power Query, et pourquoi ça change tout ?

Power Query est un moteur de transformation de données intégré à Excel depuis 2016 dont le rôle est de connecter Excel à des sources externes ou internes, de nettoyer les données brutes, de les restructurer, puis de les charger dans une feuille sous forme de tableau propre, le tout sans formule, sans macro et sans copier-coller manuel.

Dans le monde de l'informatique décisionnelle, ce type d'outil s'appelle un ETL : Extract (extraire les données), Transform (les nettoyer et les transformer), Load (les charger dans l'outil de travail). C'est exactement Power Query : tu extrais depuis un fichier CSV, une base de données, un site web ou un autre classeur, tu transformes en quelques clics, et tu charges dans Excel, prêt à analyser.

La vraie révolution, c'est la reproductibilité. Imagine que tu reçois un rapport brut chaque lundi matin, avec 5 000 lignes, des colonnes mal nommées, des dates en texte et des doublons, soit 45 minutes de nettoyage manuel à chaque fois.
Avec Power Query, tu enregistres toutes les étapes une seule fois, si bien que la semaine suivante tu colles le nouveau fichier, tu cliques sur « Actualiser » et le nettoyage se rejoue en deux secondes. Là où le copier-coller te condamnait à tout recommencer, l'outil te rend le travail une bonne fois pour toutes.

Dans la vraie vie

Un contrôleur de gestion reçoit les extractions de ventes de trois régions dans des fichiers séparés, avec des formats légèrement différents (virgule contre point-virgule, noms de colonnes inconsistants, lignes de total à supprimer). Power Query combine les trois fichiers, normalise les colonnes, retire les lignes parasites et charge le tout dans un tableau unique. Chaque mois, il remplace les fichiers dans un dossier et actualise : terminé en 10 secondes au lieu de 30 minutes.

Power Query n'est pas réservé aux experts, parce que son interface est entièrement graphique et que chaque transformation est accessible depuis un menu clic droit ou un bouton dans le ruban. Les cas d'usage les plus courants se règlent sans jamais ouvrir l'éditeur de code.

Où trouver Power Query dans Excel ?

Où le trouver :Données›Obtenir des données

Power Query se trouve dans l'onglet Données du ruban Excel, dans le groupe « Obtenir et transformer des données » (ou « Récupérer et transformer » selon ta version). C'est là que tout démarre.

Sur Microsoft 365 et Excel 2021Le bouton principal s'appelle « Obtenir des données » et ouvre un menu déroulant avec toutes les sources disponibles. C'est la version la plus complète, celle qui embarque le plus de connecteurs.

Sur Excel 2016 et 2019Power Query est intégré, mais l'interface diffère légèrement, puisque tu trouveras les boutons « Nouvelle requête », « À partir d'un fichier » et « À partir d'une base de données » dans ce même onglet Données.

Sur Excel 2010 et 2013Power Query existe en complément gratuit à télécharger sur le site de Microsoft, et une fois installé, un onglet « Power Query » apparaît dans le ruban avec un comportement identique.

L'éditeur Power Query est une fenêtre séparée d'Excel : quand tu l'ouvres, Excel continue de tourner en arrière-plan, tu travailles dans l'éditeur, tu valides tes transformations, et tu reviens dans ta feuille avec les données chargées. Pense à cet éditeur comme à un atelier de nettoyage : tu y prépares les données et tu les livres dans Excel quand elles sont prêtes.

Le saviez-vous ?

Power Query est aussi disponible sur Mac depuis Excel pour Microsoft 365 (version 16.x), avec des fonctionnalités identiques à Windows. Certains connecteurs comme SQL Server ou SharePoint peuvent toutefois demander des étapes supplémentaires selon ta configuration réseau.

Importer tes données pas à pas

Prenons le cas le plus courant, celui où tu importes un fichier CSV ou Excel pour l'ouvrir dans l'éditeur Power Query et commencer à le transformer. Quatre étapes suffisent, et chacune correspond exactement à ce que tu cliques à l'écran.

Comment importer tes données dans Power Query en 4 étapes

1. 1

   Ouvrir l'éditeur Power Query. Va dans l'onglet Données, puis clique sur « Obtenir des données » > « À partir d'un fichier » > « À partir d'un classeur Excel » (ou CSV, texte...). L'éditeur Power Query s'ouvre dans une nouvelle fenêtre.

2. 2

   Choisir la source et la table. Une boîte de dialogue liste les feuilles et tableaux disponibles dans le fichier. Sélectionne la table source. Tu vois un aperçu des données à droite. Clique sur « Transformer les données » pour ouvrir l'éditeur complet.

3. 3

   Appliquer tes transformations. L'éditeur affiche tes données en prévisualisation. Chaque action que tu fais (supprimer une colonne, changer un type, filtrer...) s'ajoute comme une étape dans le volet « Étapes appliquées » à droite.

4. 4

   Charger dans Excel. Quand les données sont propres, clique sur « Fermer et charger » dans le ruban de l'éditeur. Excel crée un nouveau tableau avec les données transformées. Toutes tes étapes sont mémorisées et rejouées à chaque actualisation.

Vrai ou faux ?

« Power Query ne sait importer que des fichiers Excel ou CSV. » Faux : il se branche sur un large éventail de sources, et la liste ci-dessous n'en couvre que l'essentiel.

• Fichiers locaux : CSV, Excel (.xlsx, .xlsm), texte (.txt), JSON, XML, PDF.
• Bases de données : SQL Server, Access, Oracle, MySQL, PostgreSQL.
• Services en ligne : SharePoint, OneDrive, Salesforce, Google Analytics.
• Web : extraction d'un tableau depuis une page HTML (scraping léger).

L'interface de l'éditeur en détail

Une fois l'éditeur ouvert, tu retrouves quatre zones qui structurent tout ton travail, et les repérer dès le départ t'évite de chercher tes boutons à chaque transformation.

• 1Le ruban en haut : onglets Accueil, Transformer, Ajouter une colonne, Affichage. Les transformations les plus courantes sont dans Accueil et Transformer.
• 2Le volet gauche (Requêtes) : liste de toutes tes requêtes. Tu peux en avoir plusieurs dans le même fichier (une par source, des requêtes de fusion, etc.).
• 3La grille centrale : prévisualisation des données après les transformations. Ce que tu vois ici correspond exactement à ce qui sera chargé dans Excel.
• 4Le volet droit (Étapes appliquées) : la liste chronologique de toutes tes transformations. C'est le cœur de Power Query. On en parle dans la section suivante.

Les transformations courantes que tu utiliseras tout le temps

Power Query propose des dizaines de transformations, mais six d'entre elles reviennent dans presque tous les projets de nettoyage.
Chacune part d'un clic droit sur un en-tête de colonne ou d'un bouton du ruban, puis s'ajoute comme une étape rejouable, si bien que tu poses le geste une fois et qu'il se rejoue à chaque actualisation.

Fractionner une colonneUne colonne « Prénom Nom » à séparer en deux ? Clic droit sur l'en-tête, puis Fractionner la colonne par délimiteur (l'espace), et Power Query crée deux colonnes en une seconde. Le même réflexe règle les dates collées, les codes produits et les adresses entassées sur une seule ligne.

Supprimer les doublonsSélectionne la colonne d'identifiant, puis Accueil > Supprimer les lignes > Supprimer les doublons. Tu peux aussi dédoublonner sur une combinaison de colonnes, par exemple un même client le même jour, en sélectionnant plusieurs en-têtes avant l'action.

Dépivoter des colonnesTes données arrivent en tableau croisé avec Jan, Fév, Mar en colonnes, alors qu'il te faut une colonne « Mois » et une colonne « Valeur » ? Sélectionne les colonnes de mois, clic droit > Dépivoter les colonnes, et le tableau bascule en format long, prêt pour un TCD ou un graphique.

Fusionner des tablesTu as une table de commandes et une table clients à rapprocher ? Accueil > Fusionner des requêtes, où tu choisis la colonne de jointure (l'ID client) et le type de jointure (gauche, intérieure...). Power Query assemble alors les deux tables en une seule étape, l'équivalent d'un RECHERCHEX appliqué à toute la table d'un coup.

Remplacer des valeursDes cellules vides, des textes inconsistants (« N/A », « - », « nd ») ou des fautes de frappe répétées ? Accueil > Remplacer les valeurs, où tu saisis la valeur à trouver et celle de remplacement, et le changement s'applique à toutes les lignes en une seule étape, sans parcourir le tableau manuellement.

Changer les types de donnéesPower Query détecte les types automatiquement, mais il se trompe parfois, notamment pour une date importée en texte ou un nombre à virgules mal interprété. Clique sur l'icône devant l'en-tête de colonne pour forcer le bon type (Date, Nombre entier, Texte ou Décimal), car un type correct dès le départ évite la plupart des erreurs de calcul en aval.

Autres transformations utiles

Le volet « Étapes appliquées » : le moteur de la reproductibilité

À droite de l'éditeur Power Query, le volet « Étapes appliquées » liste chaque transformation dans l'ordre chronologique, et c'est de loin la fonctionnalité la plus importante de l'outil.
Sans elle, Power Query ne serait qu'un nettoyeur ponctuel, alors qu'avec elle tu obtiens un pipeline entièrement automatisé qui se rejoue d'un clic. Tout ce qui rend l'outil reproductible passe par ce volet.

Chaque étape porte un nom généré automatiquement (« Source », « Type modifié », « Lignes filtrées »...). Tu peux renommer chaque étape pour que le pipeline soit lisible par quelqu'un d'autre : clique sur l'icône crayon à gauche du nom pour le modifier.

Ce que tu peux faire avec chaque étape

Le conseil du pro

Renomme systématiquement tes étapes avec des noms métier : « Supprimer lignes vides », « Normaliser la colonne région », « Filtrer année en cours ». Quand tu reviens sur la requête six mois plus tard, ou qu'un collègue doit la modifier, le pipeline se lit sans avoir à décoder le code M.

Actualiser tes données : l'opération centrale

Une fois ta requête chargée dans Excel, les données sources finissent toujours par changer, et c'est là que l'actualisation entre en jeu.
Elle rejoue toutes les étapes appliquées sur les nouvelles données et met à jour le tableau en une seule action, et c'est précisément cette mécanique qui rend Power Query si puissant au quotidien.

Comment actualiser tes données en 3 méthodes

Selon que tu es déjà dans la feuille ou que tu veux automatiser, tu disposes de trois méthodes complémentaires.

1. 1

   Clic droit sur le tableau. Dans Excel, clic droit sur le tableau puis « Actualiser ». C'est le moyen le plus rapide quand tu es déjà dans la feuille.

2. 2

   Données > Actualiser tout . Le bouton du ruban relance toutes les requêtes du classeur en une seule fois, pratique dès que tu jongles avec plusieurs sources.

3. 3

   Actualisation automatique à l'ouverture. Clic droit sur le tableau, Propriétés de la table, puis coche « Actualiser les données lors de l'ouverture du fichier ». Excel met alors les données à jour dès que tu ouvres le classeur.

Changer le chemin de la source

Si ton fichier source est déplacé ou renommé, la requête affiche une erreur, et il te faut alors lui réindiquer où chercher. Passe par Données > Requêtes et connexions > clic droit sur la requête > Modifier, puis sélectionne la première étape « Source » dans le volet Étapes appliquées et corrige le chemin dans la barre de formule.

Il existe une meilleure pratique : définir le chemin de la source comme paramètre. Ainsi, tu changes le paramètre une seule fois et toutes les requêtes qui utilisent cette source se mettent à jour automatiquement. Le paramétrage est accessible via Accueil > Gérer les paramètres dans l'éditeur Power Query.

Combiner plusieurs fichiers d'un dossier

C'est la fonctionnalité qui déclenche le plus d'enthousiasme chez ceux qui la découvrent. Imagine un dossier avec 12 fichiers Excel, un par mois, que tu veux consolider en une seule table.
Power Query le fait automatiquement et, mieux encore, il intègre chaque nouveau fichier dès la prochaine actualisation, sans que tu aies la moindre manipulation à refaire.

Comment combiner plusieurs fichiers d'un dossier en 6 étapes

1. 1

   Pointe vers le dossier. Données > Obtenir des données > À partir d'un fichier > À partir d'un dossier, puis navigue vers le dossier qui contient tes fichiers.

2. 2

   Lance la combinaison. Power Query liste tous les fichiers du dossier. Clique sur « Combiner et transformer les données » (ou « Combiner » selon ta version).

3. 3

   Choisis un fichier exemple. Une boîte de dialogue te demande un fichier exemple pour configurer les transformations. Sélectionne l'un de tes fichiers, puis choisis la feuille ou la table à importer.

4. 4

   Laisse Power Query créer les requêtes. L'outil génère automatiquement une requête principale et une requête « Transformer le fichier exemple », cette dernière contenant les transformations à appliquer à chaque fichier.

5. 5

   Applique tes transformations. Travaille dans la requête exemple (supprime des colonnes, change les types, filtre...). Tout ce que tu poses ici sera rejoué sur l'ensemble des fichiers.

6. 6

   Ferme et charge. Excel consolide tous les fichiers en une seule table. Une colonne « Source.Name » apparaît automatiquement avec le nom du fichier d'origine, pour que tu saches d'où vient chaque ligne.

Le mois suivant, tu ajoutes le nouveau fichier dans le dossier et tu actualises. Power Query le détecte automatiquement et l'intègre à la table consolidée. Aucune manipulation supplémentaire.

L’erreur fatale

Tous les fichiers du dossier doivent impérativement partager la même structure, c'est-à-dire les mêmes colonnes, dans le même ordre, avec les mêmes noms d'en-tête. Si un fichier ajoute une colonne ou change un nom, Power Query lèvera une erreur sur ce fichier précis, alors veille à standardiser tes exports en amont.

Une intro légère au langage M

Chaque étape que tu crées dans l'interface graphique génère automatiquement du code dans le langage M (Power Query Formula Language). Tu n'as pas besoin d'écrire ce code pour commencer, mais le connaître un minimum te permettra de débloquer des situations que l'interface seule ne peut pas gérer.

Pour voir le code M d'une requête, ouvre l'éditeur Power Query et clique sur Affichage > Éditeur avancé. Tu verras quelque chose qui ressemble à ceci :

let
    Source = Excel.Workbook(File.Contents("C:\donnees\ventes.xlsx"), null, true),
    Feuil1_Sheet = Source{[Item="Feuil1",Kind="Sheet"]}[Data],
    #"En-têtes promus" = Table.PromoteHeaders(Feuil1_Sheet, [PromoteAllScalars=true]),
    #"Type modifié" = Table.TransformColumnTypes(#"En-têtes promus",{
        {"Date", type date},
        {"Montant", type number},
        {"Region", type text}
    }),
    #"Lignes filtrées" = Table.SelectRows(#"Type modifié", each [Montant] > 0)
in
    #"Lignes filtrées"

La structure du code M reste simple, puisqu'il s'agit d'une série de variables assignées dans un bloc let, la dernière étant retournée dans le bloc in. Et le plus parlant, c'est que chaque ligne correspond exactement à une étape du volet « Étapes appliquées ».

La documentation officielle de Microsoft liste toutes les fonctions M avec des exemples. Pour débuter, la meilleure stratégie est de laisser l'interface générer le code puis de lire ce qu'elle a produit : tu apprends la syntaxe par observation, sans avoir à mémoriser les fonctions une par une.

Quand utiliser Power Query, les formules ou un TCD ?

Les trois outils ne s'opposent pas, ils se complètent, puisque chacun couvre un moment bien précis du travail sur les données.
Plutôt que de te demander lequel est « le meilleur », demande-toi à quelle étape tu te trouves, et le bon outil devient évident.

Power Query sert à nettoyer et structurer. C'est l'outil à sortir quand :

• tes données viennent d'une source externe (CSV, base de données, autre classeur) ;
• tu dois répéter les mêmes opérations de nettoyage régulièrement ;
• tu travailles avec plusieurs sources à consolider ;
• la structure des données doit être transformée (transposer, dépivoter, fusionner).

Les formules Excel servent à calculer dynamiquement. Elles prennent le relais quand :

• tu veux un résultat qui se recalcule selon les saisies de l'utilisateur ;
• tu dois faire des calculs conditionnels sur des données déjà propres (SOMME.SI.ENS, SI, RECHERCHEX) ;
• tu travailles sur un jeu de données relativement petit et stable.

Le tableau croisé dynamique sert à analyser et croiser. Tu y passes quand :

• tu dois agréger, regrouper et croiser des données (sommes par catégorie, comptages, moyennes) ;
• tu veux changer l'angle d'analyse rapidement sans modifier les données ;
• tes données sources sont déjà propres, ou nettoyées en amont par Power Query.

Ce qu’il faut retenir

Le pipeline idéal enchaîne plusieurs maillons : source brute (CSV ou base de données) → Power Query (nettoyer, structurer) → tableau Excel propre → TCD (agréger, croiser) → graphique (visualiser). Les formules interviennent dans la couche intermédiaire si tu as besoin de calculs que le TCD ne couvre pas. C'est ce pipeline que les contrôleurs de gestion et les data analysts utilisent au quotidien.

Erreurs fréquentes avec Power Query et comment les corriger

Power Query affiche ses erreurs dans la grille de prévisualisation (cellules en rouge avec « Error ») et dans la barre d'état en bas. Voici les erreurs les plus courantes et comment les corriger.

Le conseil du pro

Quand une requête génère des erreurs, remonte étape par étape dans le volet Étapes appliquées en cliquant sur chacune. La première étape qui affiche des cellules rouges est la source du problème, et cette méthode reste bien plus rapide que de décortiquer le message d'erreur global.

Tu sais maintenant importer, nettoyer et combiner tes données, puis rejouer tout ce travail d'un clic. Power Query t'a donné une table propre, mais souvent les données vivent dans plusieurs tables qu'il faut relier entre elles, et c'est précisément là que commence la prochaine leçon sur Power Pivot, où tu apprends à relier ces tables par une clé, à écrire tes premières mesures en DAX et à brancher un tableau croisé sur l'ensemble.