Contact: 514-605-7112 / info@lecfomasque.com

Power Query : Combiner plusieurs fichiers Excel

Publié le : 26 décembre 2014

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.

.


NOTRE OFFRE DE FORMATIONS


Nos formations sont éligibles à la subvention PACME du Québec

et notre entreprise est datadockée en France

Microsoft Most Valuable Professional

Le CFO masqué vous offre un vaste choix de formations Excel et Power BI, réparties dans 8 catégories: Excel – Tableaux de bord, Excel – Modélisation financière, Excel – VBA, Excel – Power Tools, Excel – Ninja, Power BI, Finance corporative et Compléments avancés. Ces formations sont offertes en classe, en entreprise et en ligne. Nos formateurs sont des experts dans leur domaine et ils sont accrédités par Emploi-Québec et vous remettent un certificat, à la fin de chaque formation, que vous pouvez notamment utiliser pour faire reconnaître des heures de formation continue auprès de votre ordre professionnel.

 

Découvrez quelles formations vous conviennent

Pour info: 514-605-7112 ou info@lecfomasque.com

 

Sophie Marchand

Sophie Marchand, M.Sc., CPA, CGA, MVP, est détentrice d’une M.Sc. en finance corporative et d’un titre comptable CPA, CGA. Elle est également MVP Office Apps and Services (Excel) et MVP Data Platform (Power BI). Le titre de MVP est un titre honorifique remis par Microsoft et signifie « Most Valuable Professional ». Il est attribué à des experts de solutions Microsoft qui se distinguent non seulement par leurs compétences mais également par leur implication dans la communauté. Seuls 4 professionnels possèdent cette double-qualification dans le monde. Mme Marchand cumule de nombreuses années d’expérience dans le milieu des affaires et se spécialise en modélisation financière et en intelligence d’affaires avec Excel et Power BI. Après avoir longtemps offert ses services comme consultante, elle se consacre désormais à la formation, sous toutes ses formes (formations en classe, formations en ligne, formations en entreprise, webinaires, animation de groupe d'usagers, rédaction d'articles de blogue, animation de forum en ligne et plus encore). Vous pouvez d'ailleurs la retrouver à la tête du groupe d'usagers Montreal Modern Excel and Power BI.

  • réponse philippe ,

    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

    • réponse Sophie Marchand, M.Sc., CPA, CGA, MVP ,

      Bonjour,

      Si la personne ne « rafraîchit » pas les requêtes Power Query, elle pourra en effet manipuler les TCD. Sinon, il lui faudra aussi une version d’Excel avec Power Query. L’autre option est d’utiliser la plateforme en ligne Power BI (qui est un sharepoint en ligne). Pour plus d’info à ce sujet, vous pouvez consulter le site suivant: https://powerbi.microsoft.com/.

      Au plaisir,

      Sophie

    • réponse LJ01 ,

      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

      • réponse Sophie Marchand, M.Sc., CPA, CGA, MVP ,

        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

      • réponse LJ01 ,

        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

        • réponse Sophie Marchand, M.Sc., CPA, CGA, MVP ,

          Bonjour Laurent,

          Est-ce que vos tableaux sont dans le même fichier Excel où vous faites la requête Power Query ou est-ce qu’ils sont dans des fichiers externes?

          Sophie

          • réponse LJ01 ,

            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

            • réponse Jimmy Ng ,

              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

              • réponse Sophie Marchand, M.Sc., CPA, CGA, MVP ,

                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

              • réponse S. Minne ,

                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)

                Laisser un commentaire