Formation Excel Power Query : automatiser l’import et le nettoyage des données

Power Query transforme une suite de manipulations manuelles en processus reproductible et actualisable. La vraie compétence consiste à fiabiliser toute la chaîne.

Dans beaucoup d’entreprises, la préparation des données consomme plus de temps que leur analyse. Chaque mois, quelqu’un exporte des fichiers, supprime des lignes d’en-tête, corrige des formats, colle des colonnes côte à côte et reconstruit un tableau. Le résultat est utile, mais il n’est ni traçable ni reproductible : si la personne change, ou si un export évolue, tout est à refaire.

À quoi sert réellement Power Query

Power Query est un moteur de préparation et de transformation de données intégré à Excel, piloté par une interface graphique. Chaque manipulation est enregistrée sous forme d’étape dans une requête, et l’ensemble constitue une recette rejouable. En arrière-plan, ces étapes s’écrivent dans un langage dédié, le langage M, que l’on peut consulter et modifier lorsque l’interface ne suffit pas.

L’intérêt principal n’est donc pas de gagner quelques clics : c’est de séparer la source, la transformation et le résultat. La source reste intacte, la transformation est documentée par ses étapes, et le résultat se régénère par une actualisation. Une erreur se corrige à l’endroit où elle a été introduite, sans refaire l’ensemble de la chaîne.

Les signes qu’une équipe en a besoin

• Un même retraitement est refait à l’identique chaque semaine ou chaque mois
• Des copier-coller entre fichiers alimentent un tableau de synthèse
• Les exports arrivent avec des lignes de titre, des totaux intercalés ou des colonnes fusionnées
• Les mêmes corrections de format reviennent systématiquement, notamment sur les dates et les nombres
• Personne ne sait précisément comment le fichier final a été construit
• Le classeur devient lent et difficile à ouvrir à force de formules empilées

Huit cas d’usage fréquents

• Consolider plusieurs exports mensuels ayant la même structure en une seule table
• Regrouper automatiquement tous les fichiers déposés dans un dossier de travail
• Nettoyer un export comptable ou commercial avant analyse
• Dépivoter un tableau où les mois figurent en colonnes pour obtenir un format exploitable
• Rapprocher deux sources par une clé commune, par exemple un code client
• Normaliser des libellés hétérogènes issus de saisies libres
• Filtrer et agréger un volume important sans alourdir la feuille de calcul
• Préparer une base propre destinée à un tableau croisé dynamique

Le processus en cinq temps

Une requête bien construite suit toujours la même logique. On importe d’abord la source en vérifiant l’encodage, le séparateur et la ligne d’en-tête. On nettoie ensuite : suppression des lignes inutiles, promotion des en-têtes, typage explicite des colonnes, traitement des valeurs vides. On combine si nécessaire, par ajout de lignes ou par fusion sur une clé. On contrôle enfin le résultat, avant de définir la fréquence d’actualisation.

L’étape de contrôle est celle que l’on saute le plus souvent, et c’est aussi celle qui évite les mauvaises surprises. Compter les lignes avant et après une fusion, vérifier la somme d’une colonne clé, repérer les erreurs de conversion : ces réflexes prennent quelques minutes et protègent des décisions prises sur une base fausse.

Quels prérequis avant de se former

Il n’est pas nécessaire de maîtriser les formules avancées. En revanche, quelques bases changent tout : savoir ce qu’est un tableau structuré, comprendre la différence entre une donnée et une mise en forme, être à l’aise avec les tris, les filtres et les tableaux croisés dynamiques. Une culture minimale des formats de fichiers, notamment le CSV et ses séparateurs, facilite également les premières heures.

Le positionnement initial sert précisément à mesurer ces acquis et à composer un groupe homogène, ou à prévoir une remise à niveau courte en début de parcours.

Un programme en six modules

• Module 1 — Comprendre la logique : sources, requêtes, étapes, séparation entre données brutes et résultat
• Module 2 — Importer : fichiers Excel, CSV, dossiers, tables du classeur, gestion des chemins et des accès
• Module 3 — Nettoyer : types de colonnes, valeurs manquantes, texte, dates, colonnes calculées simples
• Module 4 — Combiner : ajout de requêtes, fusions et types de jointures, gestion des correspondances absentes
• Module 5 — Restructurer : pivot, dépivot, regroupements, agrégations et colonnes conditionnelles
• Module 6 — Fiabiliser et industrialiser : paramètres, requêtes réutilisables, contrôles, documentation et actualisation

Les erreurs de typage et de source

La plupart des dysfonctionnements viennent de deux familles de problèmes. Le typage d’abord : une date interprétée au format américain, un nombre lu comme du texte à cause d’un séparateur décimal, un code produit dont les zéros initiaux disparaissent. Le typage doit être posé explicitement, tôt dans la requête, et non subi.

La source ensuite : un chemin de fichier propre à un poste, un fichier renommé, une colonne ajoutée en amont, une feuille déplacée. Ces changements cassent la requête. Utiliser des paramètres pour les chemins, s’appuyer sur des noms de colonnes plutôt que sur des positions et prévoir une gestion des colonnes inattendues rendent la chaîne beaucoup plus robuste.

Les contrôles de fiabilité à installer

• Comparer le nombre de lignes en entrée et en sortie à chaque fusion
• Vérifier une somme de contrôle sur une colonne numérique de référence
• Isoler les lignes en erreur dans une requête dédiée plutôt que les supprimer
• Documenter chaque requête par un nom explicite et une description courte
• Tester l’actualisation depuis un autre poste avant de diffuser le classeur

Power Query, formules ou macros ?

Les trois approches ne s’opposent pas. Les formules restent adaptées aux calculs contextuels à l’intérieur d’une feuille. Les macros conservent leur intérêt pour automatiser des actions d’interface ou des enchaînements qui sortent du champ de la préparation de données. Power Query, lui, est le bon outil dès qu’il s’agit d’importer, de nettoyer, de combiner et de rafraîchir des données de manière répétable.

Il ne remplace pas systématiquement une base de données ni un outil de visualisation. Lorsque les volumes deviennent très importants, que plusieurs personnes doivent écrire en même temps, ou que la diffusion doit être industrialisée, la question d’une solution en amont ou en aval se pose. La formation aide à identifier cette limite plutôt qu’à la découvrir en production.

Un exercice fil rouge

Le parcours s’appuie sur un cas suivi de bout en bout : trois exports mensuels d’un même système, avec des libellés légèrement différents et une colonne ajoutée le dernier mois. Les participants importent le dossier, normalisent les colonnes, typent les données, ajoutent les lignes, rapprochent un référentiel client, isolent les correspondances manquantes, puis produisent une table propre alimentant un tableau croisé dynamique.

L’exercice se termine par un test de robustesse : on ajoute un quatrième fichier et on actualise. Si la chaîne tient sans intervention manuelle, l’objectif pédagogique est atteint. En intra, l’exercice peut être construit à partir de vos propres exports.

14 heures ou 21 heures ?

Le parcours essentiel de 14 heures couvre l’import, le nettoyage, la combinaison simple et l’actualisation, avec un cas complet. Le parcours approfondi de 21 heures ajoute les restructurations avancées, les paramètres, les requêtes réutilisables, les contrôles de fiabilité et la documentation des traitements. Un parcours personnalisé est possible après positionnement, en présentiel, en distanciel ou en format mixte.

En résumé

Power Query fait passer la préparation des données d’une suite de gestes manuels à un processus décrit, reproductible et actualisable. La compétence utile ne se limite pas à connaître les boutons : elle consiste à typer explicitement, à sécuriser les sources, à contrôler les volumes et à documenter les requêtes. Un parcours de 14 ou 21 heures, appuyé sur vos propres fichiers, suffit généralement à rendre une équipe autonome sur ses retraitements récurrents.