Excel : automatiser ses rapports avec Power Query

Ce que vous saurez faire à la fin

1. Importer automatiquement vos fichiers sources (Excel, CSV, base SQL, web)
2. Créer un rapport Power Query qui s’actualise en 1 clic
3. Nettoyer et transformer 100 000 lignes sans formule
4. Consolider plusieurs fichiers d’un dossier
5. Charger le résultat dans un TCD toujours à jour

Durée : 2h30. Pré-requis : Excel 2019+ ou 365, connaissance basique des TCD.

Étape 1 — Ouvrir l’éditeur Power Query

1. Excel > Onglet Données > Obtenir des données.
2. Menu déroulant > À partir d’un fichier > À partir d’un classeur pour un Excel. Ou À partir d’un fichier texte/CSV, À partir d’une base de données, À partir du Web.
3. Sélectionnez le fichier > Importer.
4. Fenêtre Navigateur : cochez la feuille ou table à importer > cliquez Transformer les données (pas Charger).
5. L’éditeur Power Query s’ouvre : ruban en haut, aperçu au centre, étapes appliquées à droite.

Étape 2 — Promouvoir la première ligne en en-têtes

1. Si la première ligne contient les titres de colonnes : onglet Accueil > Utiliser la première ligne comme en-têtes.
2. Une étape apparaît à droite : En-têtes promus. Cliquez dessus pour revenir, clic droit > Supprimer pour annuler.
3. Toute transformation crée une étape. Vous pouvez revenir en arrière sans perdre le travail.

Étape 3 — Typer chaque colonne correctement

1. Cliquez sur l’icône à gauche du nom de colonne (ABC, 123, date…).
2. Choisissez le bon type : Nombre décimal, Date, Texte, Booléen.
3. Pour une colonne Monétaire : Devise.
4. Types natifs M (pour référence dans les formules) :

type text          → texte
type number        → nombre (décimal)
Int64.Type         → entier long
Currency.Type      → monétaire
Percentage.Type    → pourcentage
type date          → date
type datetime      → date et heure
type logical       → VRAI/FAUX

Étape 4 — Supprimer colonnes et lignes inutiles

1. Sélectionnez les colonnes à retirer (Ctrl+clic pour multi). Clic droit > Supprimer les colonnes.
2. Pour garder uniquement certaines : Accueil > Choisir les colonnes.
3. Supprimer lignes vides : Accueil > Supprimer les lignes > Supprimer les lignes vides.
4. Supprimer lignes d’en-tête répétées : Supprimer les lignes > Supprimer les premières lignes > N.
5. Filtrer les lignes : cliquez sur la flèche de l’en-tête > Filtres numériques/textuels.

Étape 5 — Nettoyer les données textuelles

1. Colonne avec majuscules mixtes : clic droit > Transformer > majuscule ou minuscule ou Capitalisation.
2. Espaces invisibles : Transformer > Rogner (trim).
3. Caractères spéciaux : Transformer > Nettoyer.
4. Remplacement : clic droit > Remplacer les valeurs.
5. Exemple pratique :

= Table.TransformColumns(Source, {
    {"Ville", each Text.Proper(Text.Trim(_)), type text},
    {"Telephone", each Text.Remove(_, {" ", "-", ".", "(", ")"}), type text}
  })

Étape 6 — Ajouter une colonne calculée

1. Ajouter une colonne > Colonne personnalisée.
2. Nom : CA.
3. Formule :

= [Quantite] * [PU] * (1 - [Remise])

4. OK. Nouvelle colonne ajoutée.
5. Autres colonnes utiles :

// Année d'une date
= Date.Year([Date])

// Formatage conditionnel
= if [CA] > 1000000 then "Grand" else if [CA] > 100000 then "Moyen" else "Petit"

// Texte concaténé
= Text.Combine({[Prenom], [Nom]}, " ")

Étape 7 — Consolider 12 fichiers d’un dossier

1. Données > Obtenir des données > À partir d’un fichier > À partir d’un dossier.
2. Chemin : C:\Rapports\Ventes > OK.
3. Liste des fichiers affichée > cliquez Combiner > Combiner et transformer les données.
4. Choisissez la feuille ou table commune dans le premier fichier. OK.
5. Power Query crée une fonction fnTransform et une requête Fichiers Transformer.
6. Le résultat consolide automatiquement tous les fichiers, avec une colonne Source.Name indiquant le fichier d’origine.
7. Code M généré :

let
    Source = Folder.Files("C:\Rapports\Ventes"),
    Filtre = Table.SelectRows(Source, each [Extension] = ".xlsx"),
    Invoke = Table.AddColumn(Filtre, "Data", each Excel.Workbook([Content]){0}[Data]),
    Expand = Table.ExpandTableColumn(Invoke, "Data", {"Date","Client","CA"}, {"Date","Client","CA"})
in
    Expand

Étape 8 — Fusionner deux requêtes (JOIN)

1. Vous avez une requête Ventes et une requête Produits pour enrichir avec la catégorie produit.
2. Dans Ventes : Accueil > Fusionner des requêtes > Fusionner des requêtes.
3. Table de droite : Produits.
4. Cliquez la colonne CodeProduit dans Ventes, puis Code dans Produits (colonnes clés).
5. Type de jointure :
   • Left Outer : garde toutes les lignes de Ventes (défaut)
   • Inner : uniquement les lignes avec correspondance
   • Right Outer, Full Outer, Left Anti, Right Anti pour cas avancés
6. OK. Une nouvelle colonne Produits apparaît avec des valeurs Table.
7. Cliquez l’icône d’expansion à droite du titre > cochez les champs à récupérer (Categorie, Famille) > OK.

Étape 9 — Grouper et agréger

1. Pour calculer CA total par commercial : Accueil > Grouper par.
2. Grouper par : Commercial.
3. Nouveau nom de colonne : CA_Total. Opération : Somme. Colonne : CA. OK.
4. Pour plusieurs agrégations en même temps : mode Avancé, ajoutez des lignes :

= Table.Group(Source, {"Commercial"}, {
    {"CA_Total", each List.Sum([CA]), type number},
    {"Nb_Commandes", each Table.RowCount(_), Int64.Type},
    {"Panier_Moyen", each List.Average([CA]), type number},
    {"Dernier_CA", each List.Max([Date]), type date}
  })

Étape 10 — Pivoter / dépivoter

1. Votre tableau a une colonne Mois en format large (Janvier, Février… en colonnes). Vous voulez en long.
2. Sélectionnez les colonnes à conserver (Commercial, Région). Clic droit > Dépivoter les autres colonnes.
3. Résultat : 2 colonnes (Attribut = mois, Valeur = CA).
4. Inverse : Pivoter. Sélectionnez la colonne Attribut > Transformer > Colonne pivot > Valeurs : Valeur, Fonction : Somme.

Étape 11 — Paramétrer une requête

1. Accueil > Gérer les paramètres > Nouveau paramètre.
2. Nom : pAnnee. Type : Nombre entier. Valeur : 2026.
3. Dans votre requête, modifiez un filtre pour utiliser le paramètre :

= Table.SelectRows(Source, each Date.Year([Date]) = pAnnee)

4. Pour changer l’année : modifiez le paramètre dans l’éditeur, toutes les requêtes qui l’utilisent se mettent à jour.

Étape 12 — Charger vers Excel

1. Accueil > Fermer et charger > Fermer et charger dans.
2. Options :
   • Tableau : affiche les données dans une feuille (pour < 100 000 lignes)
   • Rapport de TCD : crée directement un TCD connecté
   • Connexion uniquement : ne charge pas dans feuille mais reste disponible pour TCD (gros volumes)
3. Cochez Ajouter ces données au modèle de données pour activer Power Pivot (DAX, relations entre tables).
4. OK. Les données apparaissent dans Excel.

Étape 13 — Actualisation automatique

1. Données > Requêtes et connexions.
2. Clic droit sur la requête > Propriétés.
3. Cochez :
   • Actualiser à l’ouverture du fichier
   • Actualiser toutes les N minutes (ex: 15)
   • Activer l’actualisation en arrière-plan
4. Pour actualiser manuellement : Données > Actualiser tout ou Ctrl+Alt+F5.

Étape 14 — Gérer les erreurs

1. Colonne avec erreurs (triangle rouge) : clic droit sur l’en-tête > Remplacer les erreurs > 0 ou null.
2. Lignes à exclure : Supprimer les erreurs.
3. Fichier source manquant : M renvoie une erreur. Utilisez try...otherwise :

= try Excel.Workbook(File.Contents("C:\...\fichier.xlsx")){0}[Data]
  otherwise #table({}, {})

Étape 15 — Tester et maintenir

1. Déposez un nouveau fichier dans le dossier source.
2. Ctrl+Alt+F5 > Power Query re-traite tout.
3. Vérifiez 3 KPI critiques après actualisation. Ecarts anormaux = nouveau fichier corrompu.
4. Renommez les étapes importantes : clic droit > Renommer. Pour la lisibilité 6 mois plus tard.
5. Documentez les requêtes : clic droit > Propriétés > Description.

Erreurs courantes

• « DataFormat.Error » : une colonne avec valeurs mélangées (dates + texte). Typez en texte ou nettoyez avant.
• Colonnes qui disparaissent après actualisation : le fichier source a changé de structure. Adaptez ou créez une requête tampon.
• Très lent sur gros volumes : ajoutez Table.Buffer après une étape coûteuse pour mettre en cache.
• Privacy levels bloquent la fusion : Données > Paramètres de source de données > Options globales > décocher Toujours ignorer les niveaux de confidentialité.

Checklist

✓ En-têtes promus, types corrects sur chaque colonne
✓ Données nettoyées (espaces, casse, caractères spéciaux)
✓ Jointures bien configurées (type, colonnes clés)
✓ Paramètres pour les valeurs variables
✓ Actualisation auto à l'ouverture
✓ Gestion d'erreurs try/otherwise sur sources externes
✓ Chargé en "Connexion uniquement" si gros volumes
✓ Documenté et étapes renommées