Power Query : Combiner plusieurs fichiers Excel

Publié le 26 décembre 2014
par Sophie Marchand M.Sc.
Power Query From Folder

Si vous n’avez pas encore eu l’occasion de travailler avec Power Query, je vous le recommande fortement. Cet add-in de la suite Power BI est vraiment performant et permet d’importer, transformer et fusionner différentes sources de données. Au-delà de présenter les différentes sources de données qui peuvent être importées par Power Query, cet article montre comment combiner plusieurs fichiers Excel provenant d’un même répertoire et faire en sorte que chaque nouveau fichier déposé dans ce répertoire soit ajouté à la requête.

 

Sources de données via Power Query

 

Données provenant du web

Vous pouvez importer des données d’un site web, via son URL ou faire une recherche en ligne, directement à partir de votre fichier Excel.

Power Query Web

 

Données provenant de fichiers

Power Query permet d’importer différentes sortes de fichiers (.xlsl, .csv, .xml, .txt) et permet également d’importer tous les fichiers d’un dossier (option “From Folder”). Mais pour faire en sorte que tous les nouveaux fichiers ajoutés au dossier soient importés dans Power Query, il faudra modifier légèrement le code M. La suite de cet article vous présente comment.

Sources de données Power Query

 

Données provenant de bases de données

Power Query d’importer des données de différentes bases de données (SQL Sever, Microsoft Azure SQL, Access, Oracle, IBM DB2, MySQL, PostgreSQL, Sybase, Teradata).

Sources de données Power Query

 

Données provenant d’autres sources

Finalement, Power Query permet d’importer des données d’une multitude d’autres sources, tel qu’illustré ci-bas et Microsoft ajoute régulièrement de nouvelles options. À suivre!

Sources de données Power Query

 

Combiner des fichiers Excel

Option: “From Folder”

Pour savoir comment combiner des fichiers Excel d’un même dossier via Power Query, je vous invite à écouter la vidéo ci-bas. Mais d’abord, assurez-vous de modifier les paramètres de lecture de cette vidéo YouTube, afin de bien voir la démonstration à l’écran. Pour ce faire, nous vous recommandons de visionner la vidéo suivante en mode plein écran et en HD. Après quelques secondes, vous devriez voir l’image de façon beaucoup plus nette.

Vous trouverez ces options dans le coin inférieur droit de la vidéo.

Youtube - HD

 

Tutoriel : Comment combiner des fichiers Excel avec Power Query

À noter que pour effectuer la démarche illustrée dans cette vidéo, vous devez manipuler légèrement le code M. Pour savoir ce qu’est le code M, vous pouvez relire l’article: Power Query: Qu’est-ce que le code M? À noter que j’ai eu la chance d’apprendre le truc illustré dans cette vidéo lors du MVP Summit auquel j’ai assisté, sur le campus de Microsoft à Redmond, en novembre dernier. Si ce genre de trucs vous intéresse, le CFO masqué offre une formation sur la suite d’outils Power BI (Power Query, Power Pivot, Power View et Power Map). Sinon, certains de mes collègues MVP traitent en profondeur du sujet, comme Ken Puls, via son blogue Excel Guru. Il y a églalement le blogue BI de Chris Webb qui est très intéressant à ce niveau.

 

CFO-Masque_Formations-en-ligne_FBLa mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.

16 réflexions sur “Power Query : Combiner plusieurs fichiers Excel”

  1. Bonjour
    Si je cree des tableaux de bord utilisant power query, est ce que quelqu’un qui est sous Excel 2010 ou Excel 2013 sans accès à power BI, peut voir les résultats et modifier les tableaux (avec actualisation des TCD) ?

    Merci par avance
    Bonne journée

  2. Bonjour et merci pour ce tuto très pratique
    petite précision, comment faire la même chose mais au lieu d’appeler le tableau d’un onglet, je souhaiterai appeler le nom d’un tableau de ma feuille
    merci LJ

    1. Bonjour Laurent,

      Je ne saisis pas la différence… Tous les tableaux dans Excel ont un nom (par défaut Table1, Table2, etc. ou personnalisé). Pour importer un tableau dans Power Query, on doit cliquer sur le tableau et choisir l’option From Table / À partir du tableau. Automatiquement, la requête portera le nom du tableau (défini ou non) mais on peut modifier ce nom.

      AU plaisir,

      Sophie

  3. c’est pa rapport à le requête que je me pose des questions comment la modifier
    ici je passe par un ongletlet
    AiresCovoiturage=(FolderPath,SheetName)=>
    let
    Source = Excel.Workbook(File.Contents(FolderPath), null, true),
    Aires_de_covoiturage_19_Sheet = Source{[Item=SheetName,Kind=”Sheet”]}[Data],
    #”En-têtes promus” = Table.PromoteHeaders(Aires_de_covoiturage_19_Sheet),
    #”Type modifié” = Table.TransformColumnTypes(#”En-têtes promus”,{{“Nom de l’aire”, type text}, {“AOT”, type text}, {“Commune d’implantation”, type text}, {“Département”, type text}, {“Etat”, type text}, {“Date création/ouverture”, type date}, {“Année”, Int64.Type}, {“Axes routiers desservis”, type text}, {“Latitude”, type text}, {“Longitude”, type text}, {“Nombre d’emplacements”, Int64.Type}, {“Handicap”, type text}, {“Autres réseaux de transport”, type text}, {“Coûts HT”, Int64.Type}, {“Classe de fréquentation”, type text}, {“Classe de distance”, type text}})
    in
    #”Type modifié”
    in
    AiresCovoiturage

    et lorsque je prends le tableau3 directement quoi modifier pour passer par mon Tableau 3 car ceci ne passe pas error Détails : Key=Record Table=Table

    let
    AiresCovoiturage=(FolderPath,SheetName)=>
    let
    Source = Excel.Workbook(File.Contents(FolderPath), null, true),
    Tableau3_Table = Source{[Item=SheetName,Kind=”Table”]}[Data],
    #”Type modifié” = Table.TransformColumnTypes(Tableau3_Table,{{“Nom de l’aire”, type text}, {“AOT”, type text}, {“Commune d’implantation”, type text}, {“Département”, type text}, {“Etat”, type text}, {“Date création/ouverture”, type any}, {“Année”, Int64.Type}, {“Axes routiers desservis”, type text}, {“Latitude”, type text}, {“Longitude”, type text}, {“Nombre d’emplacements”, Int64.Type}, {“Handicap”, type text}, {“Autres réseaux de transport”, type text}, {“Coûts HT”, Int64.Type}, {“Classe de fréquentation”, type text}, {“Classe de distance”, type text}})
    in
    #”Type modifié”
    in
    AiresCovoiturage

  4. bonjour
    mes tableaux sont dans des fichiers différents
    mais bon je suis resté sur un seul tableau par page comme cela tout fonctionne sans soucis
    merci LJ

  5. Bonjour Sophie,
    le tuto est très pratique et très clair.
    Je rencontre juste un souci : j’ai des exports de différentes outils et ils n’ont pas de nom de d’onglet particulier. Des fois c’est “sheet1” et des fois c’est “Sheet1” (et je ne peux pas utiliser le nom de fichier comme dans votre exemple).
    Est-il possible de créer une fonction générique qui additionnerait les fichiers peut importe le nom de l’onglet ou dans mon cas, je dois créer deux fonction (une avec “Sheet1” comme nom d’onglet et une avec “sheet1”).

    Merci encore.

    Jimmy

    1. Bonjour Jimmy,

      Quand vous faites un “From Folder” ou “À partir du dossier”, vous pouvez ajouter une colonne personnalisée avec la formule Excel.Workbook([Content]). Vous obtiendrez alors une table, pour chaque ligne de votre requête (i.e. chaque fichier de votre dossier). Chaque table comprendra les informations suivantes:

      Name, Data, Item, Kind, Hidden

      La colonne Kind vous indiquera notamment si vous données sont dans une feuille ou dans une table (Table, Sheet). La colonne Name vous communiquera le nom de chaque feuille ou table. Vous pourrez alors faire un filtre sur Sheet dans la colonne Kind et sélectionner les noms de feuilles que vous souhaitez considérer dans la colonne Name.

      Au plaisir,

      Sophie

  6. Bonjour,

    Une petite astuce complémentaire que j’ai utilisé dans mon cas : “variabiliser” le répertoire pour que la combinaison fonctionne si les fichiers sources sont dans le même répertoire que le fichier contenant la fonction.

    1- Créer un tableau avec une seule donnée, la fonction suivante : =GAUCHE(CELLULE(“nomfichier”;A1);CHERCHE(“[“;CELLULE(“nomfichier”;A1))-1)
    La fonction INFORMATIONS(“Répertoire”) existe aussi mais elle connait des bugs (elle renvoi souvent le premier répertoire actif du fichier et non l’actuel) donc il vaut mieux l’éviter.

    2-Envoyer le tableau dans PowerQuery et le renommer “CheminFichier”

    3-Faire un Drill-down sur la valeur et l’utiliser dans l’étape “Source” de l’importation dans le code Folder.Files(CheminFichier)

  7. Bonjour Sophie,
    Comment allez-vous ? Bravo pour ce tuto très clair !
    Sous Office 365, j’ai bien réussi la démarche avec le premier onglet “GPXX” de mes différents fichiers qui s’actualisent magnifiquement lors d’ajouts de nouveaux fichiers.
    Je rencontre cependant des difficultés concernant la même opération pour le 2e onglet (GPTXX) de ces même fichiers.
    J’ai cette erreur indiqué : “Une erreur s’est produite dans la requête « FonctionPersot ». Expression.Error : Désolé… Nous n’avons pas trouvé la colonne « VSL » de la table.
    Détails :
    VSL”
    Cette colonne est pourtant bien nommée de la même façon dans tous mes fichiers, je ne comprend pas.
    Merci pour votre aide
    Antoine

  8. Bonjour,
    J’ai fait un TCD avec Power Pivot et 2 tables utilisés sont extraite de Power Query.
    J’ai donc un fichier original (base de donnée maître que j’utilise) et je copie le TCD avec différent filtre dans un nouveau classeur pour permettre aux différents utilisateurs d’aller consulter leur propre données auquel ils ont accès. Tous les usager ne peuvent pas avoir accès aux données d’un autre directeur. Par conséquent, les filtres du TDC me permettent de leur donnée accès uniquement à leur poste comptable (visualisation suivi budgétaire) et le tout est protégé par un mot de passe. Le problème est que le tableau ne fonctionne plus lorsque je fais un copie dans un autre classeur… que puis-je faire?

    De plus, j’ai fait un modèle de mise en forme pour mon TCD. Puis-je y avoir accès dans tous mes classeurs?

    Merci à l’avance et bonne journée

  9. Bonjour Sophie,
    Merci pour ce tuto, il est très bien et à la fois explicite.
    j’aimerais savoir du coup s’il y’a une limite de fichier excel que l’on pourrait combiner à travers power query? Si oui, à combien sont-ils limités?
    Aussi j’aimerais savoir si le fait de combiner plusieurs fichier excel ayant la meme structure influt sur la performance de traitement de fichier par power query? Je demande parce que j’ai essayé de combiner plusieurs fichiers excel en un seul et excel a planté, du coup je me dis que excel a une limite en terme d’exploitation de fichier.
    Merci d’avance pour votre réponse.
    Cordialement.

    1. Bonjour,
      Il n’y a pas de limite au niveau du nombre de fichiers à proprement dit. Les limites se situent au niveau de la mémoire vive disponible pour le traitement. Plus vous aurez de fichiers à traiter, plus ce sera long. Et la quantité de lignes et de colonnes de chacun des fichiers viendra aussi impacter le temps de traitement. La version Excel installée sur votre poste aussi (32 ou 64 bits),
      Vous avez les limites de Power Query pour Excel sur le site de Microsoft ici:
      https://support.microsoft.com/fr-fr/office/sp%C3%A9cifications-et-limites-de-power-query-dans-excel-5fb2807c-1b16-4257-aa5b-6793f051a9f4

      Au plaisir,
      Kim

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Retour en haut