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. Son rôle : connecter Excel à des sources de données externes ou internes, nettoyer les données brutes, les restructurer, puis les charger dans une feuille sous forme d'un tableau propre. Le tout sans formule, sans macro, 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). Power Query est l'ETL d'Excel. Tu extrais depuis un fichier CSV, une base de données, un site web ou un autre classeur. Tu transformes en quelques clics. 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 : 5 000 lignes, des colonnes mal nommées, des dates en texte, des doublons. Normalement, tu passes 45 minutes à le nettoyer à la main. Avec Power Query, tu enregistres toutes les étapes de nettoyage une seule fois. La semaine suivante, tu colles le nouveau fichier et tu cliques sur « Actualiser ». Le nettoyage se rejoue en deux secondes.

Power Query n'est pas un outil pour les experts. Son interface est graphique, chaque transformation est accessible depuis un menu clic droit ou un bouton dans le ruban. Tu verras que les cas d'usage les plus courants se règlent sans jamais ouvrir l'éditeur de code.

Où trouver Power Query dans Excel ?

Power Query se trouve dans l'onglet Données du ruban Excel. Le groupe qui t'intéresse s'appelle « 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 2021, le bouton principal s'appelle « Obtenir des données ». Il ouvre un menu déroulant avec toutes les sources disponibles. C'est la version la plus complète, avec le plus de connecteurs.

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

Sur Excel 2010 et 2013, Power Query existe en complément gratuit à télécharger sur le site de Microsoft. Une fois installé, un onglet « Power Query » apparaît dans le ruban, et le comportement est 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 Excel avec les données chargées. Pense à l'éditeur comme à un atelier de nettoyage : tu y prépares les données, et tu les livres dans ta feuille Excel quand elles sont prêtes.

Importer tes données pas à pas

On va prendre le cas le plus courant : importer un fichier CSV ou Excel et l'ouvrir dans l'éditeur Power Query pour commencer à le transformer. Voici les quatre étapes.

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

L'éditeur Power Query a quatre zones principales à connaître :

• 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. Voici les six que tu croiseras dans presque tous les projets de nettoyage de données. Chacune part d'un clic droit sur un en-tête de colonne ou d'un bouton du ruban, et s'ajoute comme une étape rejouable.

Fractionner une colonne. Une colonne « Prénom Nom » à séparer en deux ? Clic droit sur l'en-tête, puis Fractionner la colonne par délimiteur (l'espace). 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 sur une seule ligne.

Supprimer les doublons. Sé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 colonnes. Tes 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. Le tableau bascule en format long, prêt pour un TCD ou un graphique.

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

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

Changer les types de données. Power Query détecte les types automatiquement, mais il se trompe parfois : une date importée en texte, un nombre avec des 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. 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. C'est la fonctionnalité centrale : sans elle, Power Query serait juste un outil de nettoyage ponctuel. Avec elle, c'est un pipeline automatisé.

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.

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

Une fois ta requête chargée dans Excel, les données sources peuvent changer. L'actualisation rejoue toutes les étapes appliquées sur les nouvelles données et met à jour le tableau dans Excel. C'est cette mécanique qui rend Power Query si puissant en pratique.

Comment actualiser

Changer le chemin de la source

Si ton fichier source est déplacé ou renommé, la requête affiche une erreur. Pour mettre à jour le chemin : Données > Requêtes et connexions > clic droit sur la requête > Modifier > première étape « Source » dans le volet Étapes appliquées > modifier 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 l'une des fonctionnalités qui suscite le plus d'enthousiasme chez les utilisateurs qui la découvrent. Tu as un dossier avec 12 fichiers Excel (un par mois, par exemple) et tu veux tout consolider en une seule table ? Power Query le fait automatiquement, et il intègre les nouveaux fichiers au dossier dès la prochaine actualisation.

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.

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 toi-même 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 à ça :

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"

Le code M a une structure simple : une série de variables assignées dans un bloc let, la dernière étant retournée dans le bloc in. Chaque ligne correspond à 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 et de lire ce qui a été créé. Tu apprendras 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'excluent pas. Ils ont des rôles distincts et se combinent souvent. Voici comment choisir selon la situation.

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.

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 plus courantes et leurs solutions.