Excel Power Query : consolider 50 fichiers fournisseurs en 3 clics

Ce que vous saurez faire à la fin

1. Consolider 50 fichiers Excel en moins d’une minute
2. Nettoyer et normaliser automatiquement les données fournisseurs
3. Détecter doublons et écarts de prix entre fournisseurs
4. Charger le résultat dans un TCD pour reporting
5. Actualiser mensuellement en 1 clic

Durée : 45 minutes setup + 2 minutes mensuelle. Pré-requis : Excel 2019+, 50 fichiers fournisseurs .xlsx à structure identique, chemin d’accès au dossier.

Étape 1 — Préparer le dossier source

1. Créez C:\Achats\FournisseursMensuels\.
2. Déposez vos 50 fichiers : Fournisseur_001.xlsx, Fournisseur_002.xlsx, etc.
3. Vérifiez qu’ils ont la même structure : onglet Catalogue, colonnes Reference | Designation | PU | Quantite | Total.
4. Si structures varient : regroupez par format, ferez une requête par lot.

Étape 2 — Lancer Power Query depuis un dossier

1. Ouvrez un nouveau classeur Excel. Enregistrez-le Consolidation-Fournisseurs.xlsx à côté du dossier source.
2. Onglet Données > Obtenir des données > À partir d’un fichier > À partir d’un dossier.
3. Parcourez jusqu’à C:\Achats\FournisseursMensuels > OK.
4. Liste des 50 fichiers affichée. Cliquez Combiner > Combiner et transformer les données.

Étape 3 — Choisir l’onglet commun

1. Fenêtre Combiner des fichiers : sélectionnez le fichier exemple à gauche, onglet Catalogue au centre.
2. Cliquez OK.
3. Power Query crée 4 éléments :
   • Fonction Transformer le fichier
   • Requête Fichiers Transformer
   • Paramètre Fichier exemple
   • Requête principale qui consolide tout

Étape 4 — Ajouter le nom du fournisseur

1. Dans la requête principale, repérez la colonne Source.Name.
2. Clic droit > Dupliquer la colonne.
3. Renommez la copie : Fournisseur.
4. Transformation > Colonne > Extraire > Texte avant délimiteur > . (point).
5. Résultat : « Fournisseur_042 » au lieu de « Fournisseur_042.xlsx ».

Étape 5 — Nettoyer les désignations

1. Les fournisseurs saisissent « Disque SSD 500 Go »,  » DISQUE SSD 500GO « , « disque-ssd-500-go ».
2. Sélectionnez la colonne Designation > Transformer > Format > Capitalisation.
3. Ajoutez ensuite Rogner (supprime espaces début/fin).
4. Remplacer les tirets par espaces : Transformer > Remplacer les valeurs > « – » →  » « .
5. Code M généré :

= Table.TransformColumns(Source, {
    {"Designation", each Text.Proper(Text.Trim(Text.Replace(_, "-", " "))), type text},
    {"Reference", each Text.Upper(Text.Trim(_)), type text}
  })

Étape 6 — Typer chaque colonne

1. Reference : ABC (texte)
2. Designation : ABC (texte)
3. PU : 123 (Currency.Type — utilisation du format monétaire)
4. Quantite : 123 (Int64.Type — entier)
5. Total : 123 (Currency.Type)
6. Cliquez sur chaque icône et corrigez si besoin.

Étape 7 — Ajouter une colonne « Mois »

1. Pour historiser les consolidations mensuelles : Ajouter une colonne > Colonne personnalisée.
2. Nom : MoisTraitement.
3. Formule :

= DateTime.ToText(DateTime.LocalNow(), "yyyy-MM")

4. Résultat : « 2026-04 » pour tous les fichiers traités ce mois.

Étape 8 — Détecter les doublons de références

1. Créez une seconde requête : clic droit sur votre requête principale > Référencer.
2. Renommez-la Analyse Doublons.
3. Groupez par Reference :

= Table.Group(Source, {"Reference"}, {
    {"NbFournisseurs", each Table.RowCount(Table.Distinct(_, {"Fournisseur"})), Int64.Type},
    {"PUMin", each List.Min([PU]), Currency.Type},
    {"PUMax", each List.Max([PU]), Currency.Type},
    {"EcartPct", each (List.Max([PU]) - List.Min([PU])) / List.Min([PU]), Percentage.Type}
  })

4. Filtrez NbFournisseurs ≥ 2 et EcartPct > 0.2 (plus de 20 % d’écart).
5. Ces lignes sont vos priorités de négociation.

Étape 9 — Charger dans un TCD

1. Sur la requête principale : Accueil > Fermer et charger > Fermer et charger dans.
2. Sélectionnez Rapport de tableau croisé dynamique.
3. Cochez Ajouter ces données au modèle de données.
4. OK. Un TCD vide s’ouvre.
5. Glissez : Fournisseur en Lignes, Total en Valeurs (somme).
6. Vous voyez instantanément le volume d’achats par fournisseur.

Étape 10 — Actualisation mensuelle

1. Le mois suivant, déposez les nouveaux fichiers dans le même dossier.
2. Supprimez/archivez les anciens (sinon tout s’additionne). Alternative : filtrez par MoisTraitement dans le TCD.
3. Ouvrez le fichier Consolidation. Données > Actualiser tout (Ctrl+Alt+F5).
4. Power Query retraite les 50 fichiers en 30-60 secondes.
5. Validation : vérifiez le nombre total de lignes et 2-3 fournisseurs au hasard.

Erreurs courantes

• « Une erreur s’est produite dans … : l’onglet n’a pas été trouvé » : un fichier n’a pas l’onglet Catalogue. Corrigez-le ou excluez-le.
• Colonnes qui changent d’ordre : spécifiez explicitement les colonnes à extraire dans la fonction Transformer le fichier.
• Privacy Firewall : Fichier > Options > Current Workbook > Privacy > Ignore privacy levels.

Gain mesuré

Consolidation manuelle: 4 heures / mois
Power Query:            30 secondes / mois
Gain annuel: ~48 heures économisées
Plus: zéro erreur de copier-coller, audit trail intégré