Power Query : Combiner plusieurs fichiers Excel

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

Power Query : Combiner plusieurs fichiers Excel

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_FB Le CFO masqué offre un vaste choix de formations en informatique décisionnelle avec Excel et Power BI, via un portail en ligne et à distance en temps réel, selon un calendrier. Si vous désirez organiser des formations privées, faites nous simplement parvenir un courriel à info@lecfomasque.com . Des certificats convenant aux normes de formation continue des divers ordres professionnels du Québec sont offerts pour l'ensemble des formations.  

Découvrez quelles formations vous conviennent

 

13 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

Laisser un commentaire

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

Scroll to Top