Power Query : transformer et nettoyer ses données dans Excel

Ce que vous saurez faire à la fin

1. Importer 12 fichiers Excel mensuels de paie depuis un dossier en un seul clic
2. Nettoyer automatiquement les colonnes (espaces, formats date, types) avec Power Query
3. Dépivoter un tableau croisé exporté de Sage ou Saari pour le rendre exploitable
4. Fusionner deux tables (employés + salaires) avec une jointure gauche
5. Programmer un refresh automatique à l’ouverture du classeur pour vos rapports mensuels

Durée : 4h. Pré-requis : Excel 2016 ou Microsoft 365 (Power Query intégré dans le ruban Données), un dossier contenant au moins 3 fichiers Excel d’exemple (paie ou ventes), 200 Mo libres sur le disque.

Étape 1 — Comprendre pourquoi Power Query change tout

Imaginez votre comptable à Dakar qui reçoit chaque mois 12 fichiers Excel de paie depuis ses 12 agences (Thiès, Saint-Louis, Touba, Ziguinchor…). Il passe 3 jours à copier-coller, vérifier les formats de date, supprimer les lignes vides, harmoniser les noms d’employés avec espaces parasites. Power Query fait ce travail en 30 secondes, automatiquement, à chaque ouverture du fichier maître.

Power Query est un outil ETL (Extract, Transform, Load) intégré gratuitement dans Excel depuis 2016. Il enregistre toutes vos étapes de nettoyage sous forme de script (langage M) et les rejoue à chaque actualisation. Vous écrivez la procédure une fois, Excel l’applique mille fois.

Étape 2 — Activer et localiser Power Query dans Excel

Sur Excel 2016 et plus récent, Power Query est natif. Cliquez sur le ruban Données. À gauche, vous voyez le groupe Récupérer et transformer les données avec le bouton Obtenir des données. C’est votre porte d’entrée.

Sur Excel 2013, il faut télécharger le complément Microsoft Power Query (gratuit) et l’activer via Fichier > Options > Compléments > Compléments COM > Atteindre > cocher Microsoft Power Query for Excel.

Étape 3 — Importer un fichier CSV de ventes Sénégal

Préparez un fichier ventes_dakar_mars.csv avec colonnes : Date, Client, Article, Quantite, PrixUnitaire (en FCFA). Allez dans Données > Obtenir des données > À partir d’un fichier > À partir d’un texte/CSV. Sélectionnez votre fichier, cliquez Importer.

Excel ouvre une fenêtre d’aperçu. Cliquez Transformer les données (et non Charger), pour entrer dans l’éditeur Power Query. C’est ici que tout se passe.

Étape 4 — Naviguer dans l’éditeur Power Query

L’interface a 4 zones essentielles : à gauche les Requêtes (vos tables), au centre l’aperçu des données, à droite les Étapes appliquées (votre historique modifiable), en haut le ruban avec les onglets Accueil, Transformer, Ajouter une colonne, Affichage.

Chaque action que vous faites devient une étape. Vous pouvez supprimer une étape, la modifier, la renommer. C’est votre script de nettoyage visible et réversible.

Étape 5 — Nettoyer les colonnes : types, espaces, casse

Cliquez sur l’en-tête de la colonne Date, puis dans le ruban Transformer > Type de données > Date. Si Excel détecte mal le format, choisissez Utiliser les paramètres régionaux et sélectionnez Français (Sénégal) ou Français (France).

Sur la colonne Client, clic droit > Transformer > Supprimer les espaces (équivalent SUPPRESPACE). Puis Transformer > Format > Mettre en majuscules chaque mot pour harmoniser « ndiaye sa » en « Ndiaye Sa ».

Étape 6 — Importer un dossier entier (12 fichiers de paie)

C’est la fonctionnalité la plus puissante. Placez vos 12 fichiers de paie mensuels dans un dossier C:\Paie\2026\. Allez dans Données > Obtenir des données > À partir d’un fichier > À partir d’un dossier. Saisissez le chemin du dossier.

Power Query liste les 12 fichiers. Cliquez Combiner > Combiner et transformer les données. Sélectionnez la feuille à importer (ex : « Bulletins »). Power Query crée automatiquement une fonction qui ouvre chaque fichier, extrait la feuille et empile les lignes. Résultat : un tableau unique de 12 mois de paie consolidés.

Étape 7 — Dépivoter un tableau exporté de Sage ou Saari

Les exports comptables OHADA donnent souvent un format croisé : une colonne par mois (Janvier, Février, Mars…). Pour analyser, il faut le format long. Sélectionnez les colonnes des mois (Ctrl+clic), puis Transformer > Dépivoter les colonnes.

Excel transforme 12 colonnes mensuelles en 2 colonnes : Mois et Montant. Vous passez de 50 lignes × 12 colonnes à 600 lignes × 2 colonnes, prêtes pour un TCD ou Power BI.

Étape 8 — Fusionner deux tables (employés + salaires)

Vous avez une table Employes (Matricule, Nom, Poste, Agence) et une table Salaires (Matricule, MoisPaie, NetAPayer). Pour enrichir Salaires avec les noms et agences, cliquez Accueil > Fusionner les requêtes > Fusionner les requêtes comme nouvelles.

Sélectionnez Salaires comme table de gauche, Employes comme table de droite. Cliquez sur la colonne Matricule dans les deux. Type de jointure : Externe gauche (toutes depuis la première, correspondantes depuis la seconde). Validez. Une nouvelle colonne Employes apparaît, dépliez-la avec l’icône en haut à droite et choisissez Nom, Poste, Agence.

Étape 9 — Group By : agréger les ventes par agence

Sur votre table consolidée, cliquez Transformer > Regrouper par. Choisissez le mode Avancé. Regroupez par Agence, puis ajoutez 3 agrégations :

Étape 10 — Lire et modifier le code M

Cliquez Affichage > Éditeur avancé pour voir le code M qui décrit toutes vos étapes. C’est lisible et puissant. Voici un exemple typique pour une consolidation de paie :

let
    Source = Folder.Files("C:\Paie\2026"),
    FichiersExcel = Table.SelectRows(Source, each [Extension] = ".xlsx"),
    AjoutContenu = Table.AddColumn(FichiersExcel, "Donnees",
        each Excel.Workbook([Content]){[Item="Bulletins",Kind="Sheet"]}[Data]),
    Developpe = Table.ExpandTableColumn(AjoutContenu, "Donnees",
        {"Matricule","Nom","NetAPayer","MoisPaie"}),
    TypeChange = Table.TransformColumnTypes(Developpe,{
        {"Matricule", type text},
        {"Nom", type text},
        {"NetAPayer", Currency.Type},
        {"MoisPaie", type date}
    }),
    SupprimeNulls = Table.SelectRows(TypeChange, each [Matricule] <> null),
    AjoutAnnee = Table.AddColumn(SupprimeNulls, "Annee",
        each Date.Year([MoisPaie]), Int64.Type)
in
    AjoutAnnee

Étape 11 — Charger le résultat dans Excel

Une fois satisfait, cliquez Accueil > Fermer et charger > Fermer et charger dans. Choisissez Tableau et Nouvelle feuille de calcul. Pour de gros volumes (plus de 100 000 lignes), choisissez Connexion uniquement et cochez Ajouter au modèle de données pour exploiter avec un TCD Power Pivot.

Votre tableau apparaît dans Excel, formaté, prêt à être analysé. Le panneau Requêtes et connexions à droite affiche vos requêtes.

Étape 12 — Programmer le refresh automatique

Pour que la table se mette à jour à chaque ouverture du fichier, clic droit sur la requête dans le panneau Requêtes et connexions > Propriétés. Cochez :

• Actualiser lors de l’ouverture du fichier
• Actualiser toutes les 60 minutes (si vous laissez le fichier ouvert)
• Activer l’actualisation en arrière-plan

Pour un refresh manuel, ruban Données > Actualiser tout ou raccourci Ctrl+Alt+F5.

Étape 13 — Cas concret : tableau de bord paie multi-agences

Une PME sénégalaise de transport avec 8 agences et 240 employés. Avant Power Query : la DAF passait 2 jours par mois à consolider les bulletins. Après : ouverture du fichier maître, refresh, le TCD s’actualise. Résultat : CA paie consolidé par agence, ratios charges sociales (IPRES 14% + CSS 7%), top 10 salaires, alertes sur écarts mois précédent.

Le fichier maître contient 3 requêtes : Paie_Brute (depuis dossier mensuel), Employes (depuis SQL ou Excel RH), Conso_Paie (fusion + Group By). Charge initiale : 4h. Économie mensuelle : 14h.

Étape 14 — Erreurs typiques sur exports Sage/Saari OHADA

Les exports comptables ont 3 défauts récurrents : (1) lignes d’en-tête répétées tous les 50 lignes (logo + titre), (2) totaux intermédiaires en italique, (3) format date américain MM/DD/YYYY au lieu de DD/MM/YYYY. Trois étapes Power Query règlent tout :

// Supprimer les lignes contenant "TOTAL" en colonne A
SupprimeTotaux = Table.SelectRows(Source,
    each not Text.Contains([Column1], "TOTAL", Comparer.OrdinalIgnoreCase)),

// Convertir une date texte US en date française
ConvertDate = Table.TransformColumns(SupprimeTotaux, {
    {"DateOp", each Date.FromText(_, [Format="MM/dd/yyyy", Culture="en-US"])}
}),

// Filtrer les lignes vides en colonne Montant
SupprimeVides = Table.SelectRows(ConvertDate,
    each [Montant] <> null and [Montant] <> 0)

Erreurs classiques à éviter

• Erreur : nommer une requête avec un espace ou accent — le code M plante. Utilisez Conso_Paie et non Consolidation Paie 2026.
• Erreur : oublier de typer les colonnes — les sommes deviennent des concaténations de texte. Toujours Transformer > Détecter le type de données en fin de processus.
• Erreur : importer un dossier OneDrive synchronisé — chemin local instable. Utilisez le chemin SharePoint via À partir d’un fichier SharePoint.
• Erreur : refresh lent (plus de 5 min) sur un dossier de 50 fichiers — activez Affichage > Désactiver le chargement sur les requêtes intermédiaires, ne chargez que la requête finale.
• Erreur : utiliser Power Query sur Excel Online — les fonctionnalités sont limitées, travaillez en bureau pour la création.
• Erreur : modifier directement la table chargée dans Excel — vos modifs sautent au refresh. Toutes les modifs se font dans l’éditeur Power Query.

Checklist Power Query opérationnelle

✓ Excel 2016 ou Microsoft 365 installé, ruban Données visible
✓ Dossier source organisé (un sous-dossier par mois ou par agence)
✓ Fichiers source homogènes (même feuille, mêmes colonnes)
✓ Première requête créée : Données > Obtenir des données > À partir d'un fichier
✓ Types de colonnes vérifiés (Date, Nombre décimal, Texte)
✓ Étapes nommées en français pour relecture (clic droit > Renommer)
✓ Requêtes intermédiaires en "Connexion uniquement"
✓ Fusion de tables avec clé unique vérifiée
✓ Group By appliqué pour les agrégations métier
✓ Code M lu et compris dans l'éditeur avancé
✓ Refresh automatique activé à l'ouverture
✓ Bouton Actualiser tout (Ctrl+Alt+F5) testé
✓ TCD branché sur la requête Power Query
✓ Sauvegarde du fichier maître sur SharePoint ou OneDrive Pro
✓ Documentation des sources et étapes dans une feuille "Notes"