- Power Query enregistre chaque étape de nettoyage d’un fichier : au prochain export, un clic sur Actualiser rejoue toute la chaîne, sans refaire le travail à la main.
- Le piège le plus fréquent est le type de données : une date « 12/03/2026 » ou un montant « 12 000,00 » doivent être convertis avec les paramètres régionaux français, sinon la date est lue à l’envers ou le montant devient une erreur.
- Le résultat est contrôlé comme tout import : solde initial + mouvements = solde final de la banque.
- Power Query ne remplace pas les formules : il prépare des données propres ; les calculs, eux, restent dans les feuilles.
Chaque mois, le même scénario : on télécharge le relevé de la banque, on supprime les lignes de titre, on convertit les dates, on remplace les virgules, on élimine le total, et l’on recommence le mois suivant. Power Query met fin à cette routine : on fait le nettoyage une fois, Excel mémorise chaque étape, et le mois suivant un clic sur Actualiser suffit. L’épisode 18 a présenté l’import d’un CSV avec Texte en colonnes ; voici la méthode industrielle.
Ce que vous saurez faire à la fin de l’épisode
- charger des données dans l’éditeur Power Query ;
- nettoyer : lignes inutiles, en-têtes, types, vides ;
- ajouter des colonnes calculées (montant signé, mois) ;
- charger le résultat dans une feuille et l’actualiser ;
- contrôler un import et éviter le piège des paramètres régionaux.
Disponibilité. Power Query est intégré aux versions récentes d’Excel (onglet Données, groupe Obtenir et transformer des données). Les menus peuvent varier légèrement selon la version et le système ; vérifiez ce que propose votre installation avant de vous lancer.
Le cas : le relevé de mars
Voici l’export brut d’une banque, tel qu’il arrive : un titre, une ligne d’identification du compte, une ligne vide, puis les mouvements, et une ligne de total à la fin.
Figure 1 : l’export brut. Repère 1 : les lignes de titre ; repère 2 : les montants en texte ; repère 3 : la ligne de total.
Les problèmes sont classiques :
- Trois lignes avant l’en-tête (titre, compte, ligne vide).
- Des dates en texte (« 03/03/2026 ») et des montants en texte avec espace insécable comme séparateur de milliers et virgule décimale (« 12 000,00 »).
- Des cellules vides là où il n’y a pas de débit ou de crédit.
- Une ligne TOTAL qui fausserait toute somme ou tout tableau croisé dynamique.
- Aucune colonne de montant signé, alors que l’analyse en a besoin.
Les étapes dans l’éditeur
Sélectionnez la plage A4:E13 et choisissez Données > À partir d’un tableau/d’une plage (décochez « Mon tableau comporte des en-têtes » : l’en-tête n’est pas encore sur la première ligne). L’éditeur Power Query s’ouvre. À droite, le volet Étapes appliquées liste ce que vous faites ; chaque action ajoute une étape.
Figure 2 : illustration schématique de l’éditeur et de ses étapes. Les noms exacts des étapes varient selon la version et la langue.
- Supprimer les lignes du haut (Accueil > Supprimer des lignes > Supprimer les lignes du haut, 3).
- Utiliser la première ligne comme en-têtes : « Date opération », « Libellé », « Débit », « Crédit », « Solde ».
- Supprimer la ligne TOTAL : filtrez la colonne Date opération pour retirer la valeur « TOTAL ».
- Changer les types avec les paramètres régionaux : pour la date, Modifier le type > Utiliser les paramètres régionaux… puis Date avec les paramètres Français (France) ; de même pour Débit, Crédit et Solde en Nombre décimal.
- Remplacer les valeurs vides par 0 dans Débit et Crédit.
- Ajouter une colonne personnalisée nommée Montant :
[Crédit] - [Débit]. - Ajouter la colonne Mois, extraite de la date (Ajouter une colonne > Date > Mois).
L’étape 4 est la plus piégeuse. Si les paramètres régionaux de conversion sont ceux des États-Unis, « 12/03/2026 » est lu comme le 3 décembre, et « 25/03/2026 » devient une erreur (il n’existe pas de 25e mois). Le français (jour/mois/année) est indispensable pour les relevés marocains.
Le script généré (Affichage > Éditeur avancé) montre par exemple pour la colonne Montant :
= Table.AddColumn(#"Valeur remplacée", "Montant", each [Crédit] - [Débit], type number)Vous n’avez pas besoin d’écrire du code : l’éditeur le produit à partir des clics. Mais savoir qu’il existe permet de relire et de réutiliser une requête.
Charger et actualiser
Accueil > Fermer et charger écrit le résultat dans une nouvelle feuille sous forme de tableau. Le mois suivant : remplacez le contenu de la feuille source (ou pointez la requête vers le nouveau fichier), puis Données > Actualiser tout. Toutes les étapes sont rejouées et le tableau se met à jour. C’est la grande différence avec une manipulation manuelle : le travail est reproductible et documenté.
Figure 3 : le résultat attendu et son contrôle. Repère 1 : le montant signé ; repère 2 : le rapprochement avec la banque.
Le contrôle : le solde de la banque
Aucun import n’est fiable sans rapprochement. Ici, trois lignes suffisent :
Total des mouvements : =SOMME(E2:E9) → 21 289,50
Solde final recalculé : =E11+E12 → 61 289,50
Contrôle : =SI(ARRONDI(E13-E14;2)=0;"OK";"ÉCART")Solde initial 40 000,00 + mouvements 21 289,50 = 61 289,50, le solde donné par la banque. Les débits totalisent 21 110,50 (780 + 16 500 + 45 + 3 600 + 185,50) et les crédits 42 400 (12 000 + 8 400 + 22 000) : exactement les totaux du relevé brut. Si un mouvement manque ou si une conversion de type a échoué, le contrôle passe à « ÉCART ».
Ce que Power Query fait bien, et moins bien
| Bien | Moins bien |
|---|---|
| Nettoyage répété de fichiers de même structure | Fichiers dont la structure change sans prévenir |
| Fusion et consolidation de sources (épisode suivant) | Calculs financiers complexes (restent dans les feuilles) |
| Traçabilité : chaque étape est visible | Très gros volumes (au-delà du million de lignes, une base de données est préférable) |
| Aucune formule fragile dans la feuille de départ | Collaboration sans partage de la requête |
Règle pratique : Power Query prépare, les formules calculent. Le tableau chargé alimente ensuite un tableau croisé dynamique (épisode 25), une balance (épisode 32) ou un rapprochement bancaire.
Les erreurs classiques
- Mauvais paramètres régionaux : dates inversées, montants en erreur.
- Oublier la ligne de total ou les lignes de pied de page : elles faussent les sommes.
- Casser une étape en renommant une colonne source : l’actualisation échoue.
- Effacer l’export brut : conservez toujours l’original, point de départ de toute vérification.
- Charger sans contrôler : un tableau propre peut rester faux.
À vous de jouer
Dans le classeur d’exercice :
- Importez l’onglet Brut dans Power Query et reproduisez les sept étapes. Votre tableau doit correspondre à Résultat attendu.
- Quel est le total des débits ? Des crédits ? Quel est le solde final ?
- Refaites la conversion de la date avec des paramètres régionaux anglais (États-Unis) : que se passe-t-il pour le 25/03/2026 ? Pour le 12/03/2026 ?
- Dans l’onglet Résultat attendu, supprimez la ligne du 17/03 (45,00 de frais de tenue de compte) : que montre le contrôle ?
Correction. (2) Débits : 21 110,50 ; crédits : 42 400,00 ; solde final : 61 289,50. (3) Avec le format anglais (mois/jour/année), le 25/03/2026 devient une erreur (le mois 25 n’existe pas) et le 12/03/2026 est lu comme le 3 décembre 2026 : le piège est silencieux sur les dates ambiguës. (4) Le solde final recalculé devient 61 334,50 (les frais ne sont plus déduits) alors que la banque indique 61 289,50 : le contrôle passe à ÉCART, et l’écart de 45,00 est exactement le montant de la ligne manquante. C’est ce qui rend le rapprochement utile : il dit non seulement qu’il y a une erreur, mais aussi de combien.
À retenir
- Power Query enregistre le nettoyage : on le fait une fois, on actualise ensuite.
- Convertissez les types avec les paramètres régionaux français pour les dates et montants.
- Retirez les lignes de titre et de total, remplacez les vides, ajoutez les colonnes utiles.
- Contrôlez chaque import avec le solde de la banque.
- Power Query prépare les données ; les calculs et les contrôles restent dans les feuilles.
Épisode 47 : Power Query pour consolider douze balances mensuelles en une seule table.
Questions fréquentes
Quelle différence entre Power Query et Texte en colonnes (épisode 18) ?
Texte en colonnes est une opération ponctuelle : on la refait à chaque nouveau fichier. Power Query mémorise les étapes dans une requête : au prochain export, on actualise et tout est rejoué. Dès qu’un import revient chaque mois, Power Query fait gagner un temps considérable.
Mes données changent de forme d’un mois sur l’autre : que se passe-t-il ?
Si une colonne est renommée ou déplacée, une étape peut échouer (« la colonne n’a pas été trouvée »). Il faut alors corriger l’étape concernée. D’où l’intérêt de faire peu d’étapes, de les nommer clairement et de refuser les colonnes inutiles dès l’import.
Power Query modifie-t-il le fichier source ?
Non : il lit la source et produit un nouveau tableau dans le classeur. Le fichier d’origine reste intact. C’est une bonne pratique pour la traçabilité : on conserve l’export brut de la banque tel quel.
Où voir le code derrière les étapes ?
Dans l’éditeur Power Query, onglet Affichage > Éditeur avancé : on y lit le script en langage M généré par vos clics. Il est utile pour comprendre une requête reprise d’un collègue, sans qu’il soit nécessaire d’écrire du code pour débuter.
Articles rédigés par l’équipe du cabinet à partir des textes officiels et de la pratique des missions. Chaque article indique sa date de publication et, le cas échéant, de dernière revue.