Chaque mois, le même export arrive : un fichier CSV de commandes avec des espaces parasites, des dates lues comme du texte, des doublons et une colonne qui mélange deux informations. Power Query, intégré à Excel, enregistre une fois les opérations de nettoyage puis les rejoue en un clic sur le fichier suivant. Ce guide vous montre comment importer une source, la nettoyer sans formule, combiner plusieurs fichiers et rapprocher deux tables, sur un exemple de suivi des commandes.
L’exemple utilisé tout au long de l’article est un export mensuel fictif de 300 commandes issu d’un logiciel de gestion commerciale, avec six colonnes : Date, N° commande, Client, Commercial - Région, Quantité et Prix unitaire. Ses défauts sont ceux que l’on rencontre dans la plupart des exports réels.
Comprendre ce que Power Query fait à la place de vos formules
Power Query est un outil de préparation de données : il se connecte à une source, applique une suite de transformations et charge le résultat dans une feuille ou dans le modèle de données. C’est le moteur de la fonctionnalité « Obtenir et transformer » d’Excel. Sa différence avec les formules tient en un point : la source n’est jamais modifiée, et chaque transformation est enregistrée comme une étape que vous pouvez relire, modifier ou supprimer.
| Tâche | Avec des formules | Avec Power Query |
|---|---|---|
| Supprimer les espaces | SUPPRESPACE dans une colonne d’aide, puis copier-coller les valeurs | Transformer › Format › Supprimer les espaces, une seule fois |
| Séparer « Commercial - Région » | GAUCHE, DROITE et TROUVE, ou Données › Convertir | Fractionner la colonne › Par délimiteur |
| Supprimer les doublons | Données › Supprimer les doublons, à refaire à chaque import | Étape enregistrée, rejouée à l’actualisation |
| Intégrer le fichier du mois suivant | Copier-coller les données et étendre les formules | Actualiser |
Power Query ne remplace pas tout. Pour un calcul qui doit réagir instantanément à une saisie, une formule reste plus adaptée ; Power Query intervient en amont, pour livrer une table propre sur laquelle s’appuient vos formules, vos tableaux croisés dynamiques et vos graphiques.
Vérifier que votre version d’Excel inclut Power Query
Sur Windows, Power Query est intégré à Excel depuis la version 2016 : il se trouve dans l’onglet Données, groupe Récupérer et transformer des données. Les versions plus anciennes et Excel pour Mac n’offrent pas exactement les mêmes possibilités.
| Version | Power Query | Point d’attention |
|---|---|---|
| Microsoft 365 (Windows) | Intégré, mis à jour en continu | Version la plus complète |
| Excel 2016, 2019, 2021 et 2024 (Windows) | Intégré à l’onglet Données | Certains connecteurs récents absents des versions anciennes |
| Excel 2010 et 2013 (Windows) | Complément à installer | N’évolue plus, à éviter pour un nouveau projet |
| Excel pour Mac (Microsoft 365) | Éditeur Power Query disponible à partir de la version 16.69 | Moins de sources : classeur, texte/CSV, XML, JSON, SharePoint Online, OData… |
La liste exacte des sources par version est tenue à jour par Microsoft dans sa page Sources de données Power Query dans les versions Excel. Pour Mac, la page Importer et formater des données dans Excel pour Mac précise les sources prises en charge.
Importer un fichier CSV ou un classeur en 5 étapes
- Ouvrez l’onglet Données : cliquez sur Obtenir des données, puis À partir d’un fichier.
- Choisissez le type de source : À partir d’un fichier texte/CSV pour l’export, ou À partir d’un classeur Excel pour un fichier .xlsx. Sélectionnez le fichier, puis cliquez sur Importer.
- Contrôlez l’aperçu : Excel affiche les premières lignes et détecte le séparateur. Si les accents sont remplacés par des caractères étranges, changez l’origine du fichier, par exemple en 65001 : Unicode (UTF-8).
- Cliquez sur Transformer les données : plutôt que Charger, ce bouton ouvre l’éditeur Power Query, où se fait le nettoyage.
- Repérez les trois zones de l’éditeur : l’aperçu des données au centre, le volet Requêtes à gauche et, à droite, le volet Paramètres d’une requête avec la liste des Étapes appliquées.
Si vos données sont déjà dans le classeur, cliquez dedans puis choisissez Données › À partir de Tableau/Plage (le libellé varie légèrement selon les versions). Excel convertit d’abord la plage en tableau, puis ouvre l’éditeur.
Nettoyer les données dans l’éditeur : 7 transformations à connaître
Dans l’éditeur, la plupart des commandes s’appliquent à la colonne sélectionnée, depuis le ruban ou par un clic droit sur l’en-tête. Chaque action ajoute une ligne dans Étapes appliquées.
- Utiliser la première ligne pour les en-têtes : onglet Accueil, si les titres de colonnes apparaissent comme une ligne de données.
- Supprimer les espaces : sélectionnez Client, puis Transformer › Format › Supprimer les espaces. « Dupont SARL » et « Dupont SARL » suivi d’un espace ne font plus qu’un seul client.
- Harmoniser la casse : dans le même menu Format, Mettre En Majuscules À Chaque Mot uniformise les noms saisis tantôt en majuscules, tantôt en minuscules.
- Fractionner une colonne : clic droit sur Commercial - Région › Fractionner la colonne › Par délimiteur, avec le délimiteur personnalisé « - ». Renommez ensuite les deux colonnes obtenues, puis supprimez les espaces restants.
- Changer le type de données : cliquez sur l’icône à gauche de l’en-tête. Pour une date au format français lue comme du texte, choisissez Utilisation des paramètres régionaux, puis Date et Français (France).
- Filtrer les lignes inutiles : la flèche de l’en-tête N° commande permet de décocher les lignes vides ou la ligne « Total » ajoutée en bas de l’export.
- Supprimer les doublons : sélectionnez N° commande, puis Accueil › Supprimer les lignes › Supprimer les doublons.
Pour calculer le montant hors taxes, passez par Ajouter une colonne › Colonne personnalisée, nommez la colonne Montant HT et saisissez la formule ci-dessous. Les noms de colonnes s’écrivent entre crochets.
[Quantité] * [Prix unitaire]Chaque étape est écrite dans le langage M, visible dans la barre de formule de l’éditeur (onglet Affichage, case Barre de formule). Vous n’avez pas besoin de l’écrire, mais savoir le lire aide à corriger une étape. Celle qui supprime les espaces ressemble à ceci :
= Table.TransformColumns(#"Type modifié", {{"Client", Text.Trim, type text}})Contrairement aux formules de la feuille, le langage M utilise la virgule comme séparateur et des noms de fonctions en anglais, quelle que soit la langue d’Excel. Pour une colonne qui dépend d’une condition, comme « Grand compte » au-delà de 5 000 €, la commande Ajouter une colonne › Colonne conditionnelle propose un assistant sans code, équivalent de la fonction SI dans Excel.
Charger le résultat et l’actualiser chaque mois
Une fois la requête propre, cliquez sur Accueil › Fermer & charger. Excel crée une nouvelle feuille contenant un tableau, et la requête apparaît dans le volet Requêtes & connexions. Le menu Fermer & charger dans… propose d’autres destinations : une feuille existante, une simple connexion ou le modèle de données, utile pour Power Pivot.
Le mois suivant, remplacez le fichier source par le nouvel export, sous le même nom et au même emplacement, puis cliquez sur Données › Actualiser tout. Toutes les étapes sont rejouées dans l’ordre, en quelques secondes. Dans les propriétés de la requête (clic droit dans le volet Requêtes & connexions › Propriétés), l’option « Actualiser les données lors de l’ouverture du fichier » automatise cette mise à jour.
La table chargée sert ensuite de source à un tableau croisé dynamique : Actualiser tout met à jour la requête puis le tableau croisé, sans manipulation supplémentaire.
Power Query fait partie du niveau Expert et IA de la formation Excel CPF de CNFN (chapitres 17 à 23), avec Power Pivot, le langage DAX et la construction d’un tableau de bord. Il est ensuite mobilisé dans des cas pratiques métiers comme le suivi des commandes ADV ou le rapprochement bancaire, d’abord guidés puis chronométrés.
Combiner les fichiers d’un dossier en une seule table
Lorsque vous recevez un export par mois ou par agence, Power Query peut les empiler automatiquement. Placez tous les fichiers dans un même dossier, puis suivez ces étapes.
- Lancez l’import du dossier : Données › Obtenir des données › À partir d’un fichier › À partir d’un dossier, puis sélectionnez le dossier.
- Choisissez Combiner › Combiner et transformer les données : Excel vous demande de désigner un fichier exemple, qui sert de modèle pour les autres.
- Nettoyez la requête obtenue : la colonne Source.Name indique de quel fichier vient chaque ligne, pratique pour retrouver le mois ou l’agence.
- Déposez les nouveaux fichiers dans le dossier : à l’actualisation suivante, ils sont intégrés sans autre manipulation.
Cette méthode suppose que tous les fichiers ont la même structure : mêmes colonnes, mêmes en-têtes, même ordre. Un fichier avec une colonne renommée provoque une erreur ou une colonne vide. Pour empiler deux requêtes déjà créées, la commande Accueil › Combiner › Ajouter des requêtes produit le même résultat.
Rapprocher deux tables avec Fusionner des requêtes
Fusionner des requêtes joue le rôle d’une RECHERCHEX à grande échelle : elle ajoute à chaque commande des informations provenant d’une autre table, par exemple le segment et le délai de paiement de chaque client issus d’un fichier clients.
- Créez une requête pour chaque table : les commandes et les clients, chargées en connexion seule si vous n’avez pas besoin de les afficher.
- Ouvrez Accueil › Combiner › Fusionner des requêtes : choisissez la table Commandes en haut, la table Clients en bas, puis cliquez sur la colonne commune dans chacune.
- Choisissez le type de jointure : Externe gauche conserve toutes les commandes et ajoute les correspondances trouvées. Le bas de la fenêtre indique combien de lignes correspondent.
- Développez la colonne ajoutée : cliquez sur l’icône à deux flèches de son en-tête et cochez les champs à récupérer.
La jointure Anti gauche donne l’inverse : les lignes sans correspondance. Elle sert à repérer les commandes dont le client est absent du référentiel ou, en comptabilité, les opérations non rapprochées entre un relevé bancaire et le grand livre. Pour une recherche ponctuelle dans une feuille, les fonctions restent plus simples : le comparatif RECHERCHEX ou RECHERCHEV vous aide à choisir.
Éviter les erreurs qui bloquent une requête
- « Impossible de trouver le fichier » : la source a été déplacée ou renommée. Double-cliquez sur la première étape, Source, pour indiquer le nouveau chemin, ou passez par Accueil › Paramètres de la source de données.
- « La colonne … de la table est introuvable » : un en-tête a changé dans le nouvel export. Repérez l’étape en erreur dans Étapes appliquées et corrigez le nom de colonne.
- Des cellules Error dans une colonne : souvent une conversion de type impossible, comme un texte dans une colonne Montant. Après vérification, clic droit sur l’en-tête › Remplacer les erreurs, ou Supprimer les erreurs pour écarter ces lignes.
- Des jours et des mois inversés : le type Date a été appliqué avec des paramètres régionaux anglais. Supprimez l’étape Type modifié et refaites la conversion avec Utilisation des paramètres régionaux.
- Une étape ajoutée au mauvais endroit : cliquez sur une étape antérieure pour voir les données à ce stade ; une nouvelle commande s’insère après l’étape sélectionnée.
Dernier réflexe : renommez les étapes importantes (clic droit › Renommer) et les requêtes elles-mêmes. Une requête nommée « Commandes_nettoyées » se maintient bien plus facilement qu’une suite de « Personnalisé1 ». Pour savoir si votre niveau actuel vous permet d’aborder ces outils, ou s’il vaut mieux consolider d’abord les fonctions et les tableaux croisés dynamiques, remplissez le formulaire de vérification des prérequis Excel.
Questions fréquentes sur Power Query
Qu’est-ce que Power Query dans Excel ?
Power Query est l’outil d’Excel qui importe des données depuis un fichier, un dossier, une base de données ou le web, les transforme et charge le résultat dans une feuille. Chaque transformation est enregistrée comme une étape, rejouée automatiquement à chaque actualisation.
Où se trouve Power Query dans Excel ?
Sur Windows, depuis Excel 2016, dans l’onglet Données, groupe Récupérer et transformer des données : bouton Obtenir des données, puis la source voulue. La commande Lancer l’Éditeur Power Query, dans le même menu, ouvre directement l’éditeur.
Power Query est-il disponible sur Mac ?
Oui, pour les abonnés Microsoft 365 avec Excel pour Mac 16.69 ou une version ultérieure. Les sources sont moins nombreuses que sur Windows : classeurs, fichiers texte et CSV, XML, JSON, listes SharePoint Online et OData notamment.
Faut-il savoir programmer pour utiliser Power Query ?
Non. Les transformations courantes se font par les menus de l’éditeur, qui écrit lui-même le code en langage M. Savoir lire ce code aide à corriger une étape, mais l’écrire n’est utile que pour des besoins avancés.
Quelle différence entre Power Query et Power Pivot ?
Power Query prépare et nettoie les données. Power Pivot les relie entre elles dans un modèle de données et calcule des mesures en langage DAX (Data Analysis Expressions). Les deux se complètent : Power Query alimente le modèle que Power Pivot exploite.
Power Query modifie-t-il le fichier source ?
Non. Power Query lit la source sans la modifier et produit une nouvelle table. Si une transformation ne convient pas, supprimez l’étape correspondante : les données d’origine restent intactes.
Retenez la logique : importer, nettoyer une seule fois dans l’éditeur, charger, puis actualiser à chaque nouvel export. Commencez par le fichier que vous retraitez à la main chaque semaine ou chaque mois : c’est là que le gain de temps est le plus visible. Pour apprendre Power Query avec Power Pivot et les tableaux de bord, sur des cas pratiques corrigés en visioconférence, découvrez le programme de la formation Excel CPF.
