Fusionner tous les fichiers Excel d’un dossier avec Power Query

Ouvrir dix fichiers Excel un par un, copier leurs lignes, les coller à la suite dans un tableau maître : c’est l’une des tâches les plus chronophages et les plus sources d’erreurs de la bureautique de gestion. Un fichier oublié, une colonne décalée, un onglet renommé, et il faut souvent tout recommencer.

Power Query règle ce problème avec le connecteur « Depuis un dossier ». Le souci, c’est que l’assistant automatique génère trois requêtes imbriquées (le fichier exemple, la fonction de transformation, le paramètre de dossier), qui deviennent vite difficiles à relire et à déboguer une fois qu’un des fichiers source change de forme. Je vous montre ici la version manuelle : une seule requête, lisible, que vous pouvez modifier vous-même sans reconstruire tout l’assistant.

Le point de départ : lister les fichiers d’un dossier

Dans Power Query, Folder.Files("chemin") renvoie une table : une ligne par fichier du dossier, avec son nom, son extension, ses dates, et surtout une colonne Content qui contient le fichier lui-même sous forme binaire. C’est cette colonne qui sert ensuite à lire chaque classeur.

Pour l’obtenir sans passer par l’assistant complet : Nouvelle requête › Requête vide, puis dans l’éditeur avancé :

let
    Source = Folder.Files("C:\Ventes\Mensuel")
in
    Source

Adaptez le chemin à votre dossier. Si le classeur qui contient cette requête est lui-même dans un dossier partagé, préférez un chemin relatif construit avec =INFORMATIONS("REPERTOIRE") côté Excel : la technique est détaillée dans l’article sur les paramètres Power Query.

La requête complète, en une seule fois

Voici la requête qui filtre les fichiers Excel du dossier, lit chacun d’eux, et consolide le tout en gardant le nom du fichier source sur chaque ligne :

let
    Source = Folder.Files("C:\Ventes\Mensuel"),
    Filtres = Table.SelectRows(Source, each Text.EndsWith([Extension], ".xlsx")),
    AjoutDonnees = Table.AddColumn(Filtres, "Donnees", each
        Table.PromoteHeaders(
            Excel.Workbook([Content]){[Item="Feuil1", Kind="Sheet"]}[Data],
            [PromoteAllScalars=true]
        )
    ),
    Reduit = Table.SelectColumns(AjoutDonnees, {"Name", "Donnees"}),
    Deplie = Table.ExpandTableColumn(
        Reduit, "Donnees",
        Table.ColumnNames(Reduit[Donnees]{0})
    )
in
    Deplie

Comprendre la requête étape par étape

• Folder.Files("C:\Ventes\Mensuel") : liste tous les fichiers du dossier, avec leur contenu binaire dans la colonne Content.
• Table.SelectRows(…, each Text.EndsWith([Extension], ".xlsx")) : ne garde que les fichiers Excel, pour ignorer un éventuel fichier temporaire (~$rapport.xlsx) ou un PDF égaré dans le même dossier.
• Table.AddColumn(Filtres, "Donnees", each …) : ajoute une colonne dont chaque cellule est une table, le contenu d’une feuille, pour chaque ligne (donc chaque fichier) de Filtres.
• Excel.Workbook([Content]) : ouvre le classeur binaire et renvoie la liste de ses feuilles, tableaux et plages nommées.
• {[Item="Feuil1", Kind="Sheet"]}[Data] : sélectionne la feuille nommée « Feuil1 » dans cette liste, et récupère sa donnée brute (colonne Data).
• Table.PromoteHeaders(…, [PromoteAllScalars=true]) : transforme la première ligne de chaque feuille en en-têtes de colonnes.
• Table.SelectColumns(AjoutDonnees, {"Name", "Donnees"}) : ne garde que le nom du fichier et les données extraites ; le reste (dates, taille, chemin) ne sert plus.
• Table.ExpandTableColumn(Reduit, "Donnees", Table.ColumnNames(Reduit[Donnees]{0})) : déplie chaque table imbriquée en lignes, en dupliquant le nom du fichier source sur chacune. Reduit[Donnees]{0} récupère la première table de la colonne pour lire dynamiquement ses noms de colonnes, sans avoir à les taper à la main.

Les pièges qui cassent la requête

Les feuilles ne portent pas toutes le même nom

{[Item="Feuil1", Kind="Sheet"]}[Data] échoue dès qu’un seul fichier a une feuille nommée différemment. Si l’ordre des feuilles est fiable mais pas leur nom, remplacez la sélection par un index positionnel :

Excel.Workbook([Content]){0}[Data]

Ceci prend la première feuille du classeur, quel que soit son nom. L’inverse (nom fiable, ordre variable) est justement le cas où il faut garder la sélection par Item.

Erreur Formula.Firewall

Combiner une requête « dossier » avec une fonction qui lit chaque fichier déclenche parfois cette erreur de confidentialité, déjà rencontrée avec les paramètres Power Query. Le correctif rapide (Options des requêtes actuelles › Confidentialité › Ignorer les niveaux de confidentialité) fonctionne, mais seulement pour votre poste : à réappliquer si le classeur circule.

Un fichier avec une colonne en trop, ou en moins

Table.ExpandTableColumn déplie exactement les colonnes qu’on lui donne. Si un fichier du dossier a une colonne absente des autres, elle sera ignorée si elle n’apparaît pas dans Table.ColumnNames(Reduit[Donnees]{0}), c’est-à-dire si le premier fichier lu ne l’avait pas. Pour la première mise en place d’une requête de ce type, vérifiez à la main que tous les fichiers du dossier partagent bien la même structure de colonnes avant de faire confiance au résultat.

Une colonne du classeur s’appelle déjà « Name »

Folder.Files nomme sa colonne de nom de fichier Name. Si vos données contiennent elles-mêmes une colonne « Name » (ou « Nom », selon la langue de votre feuille), Table.ExpandTableColumn renommera automatiquement l’une des deux en lui ajoutant un suffixe (Name.1). Ce n’est pas une erreur, mais autant renommer vous-même la colonne source pour garder un résultat lisible, avec un quatrième argument à Table.ExpandTableColumn : la liste des nouveaux noms.

Les types de colonnes ne se stabilisent pas tout seuls

Après le dépliage, Power Query applique en général une détection automatique des types sur le résultat, mais elle se base sur un échantillon des premières lignes. Si le premier fichier lu a une colonne « Quantité » entièrement numérique, et qu’un fichier plus loin dans le dossier contient une ligne « voir remarque » dans cette même colonne, le type détecté au départ ne correspondra plus une fois toutes les lignes chargées : Power Query affichera des erreurs Error ligne par ligne plutôt que de tout faire échouer d’un coup, ce qui les rend faciles à manquer. Le réflexe à prendre : fixer les types explicitement, en dernière étape, plutôt que de laisser l’étape Type modifié générée automatiquement. Remplacez la dernière ligne de la requête par :

    Deplie = Table.ExpandTableColumn(
        Reduit, "Donnees",
        Table.ColumnNames(Reduit[Donnees]{0})
    ),
    Types = Table.TransformColumnTypes(Deplie, {
        {"Quantité", Int64.Type},
        {"Date", type date},
        {"Prix unitaire", type number}
    })
in
    Types

Avec cette étape explicite, une valeur qui ne correspond pas au type attendu remonte en erreur visible dans la colonne concernée, au lieu de fausser silencieusement une somme ou une moyenne calculée plus loin dans le rapport.

Disponibilité

Power Query (« Obtenir et transformer les données ») est intégré nativement depuis Excel 2016. Sur Excel 2010 et 2013, seul un module complémentaire séparé l’ajoute, avec des limites de fonctions. Toutes les fonctions M utilisées ici (Folder.Files, Excel.Workbook, Table.PromoteHeaders, Table.ExpandTableColumn) sont disponibles dans toutes les versions supportées de Power Query, y compris sur Excel 2016 : rien ici n’exige Microsoft 365.

Ce que ça change

Une fois la requête posée, actualiser la consolidation revient à déposer un nouveau fichier dans le dossier et cliquer sur Actualiser : plus de copier-coller, plus de fichier oublié, et une seule requête à relire quand quelque chose casse, plutôt que trois générées automatiquement. La référence complète des fonctions utilisées est dans la documentation Microsoft : Folder.Files, Excel.Workbook et Table.ExpandTableColumn.