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.
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.
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).
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!
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.
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.
La 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é.
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
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
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
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
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
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
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
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
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
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)
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
Bonjour,
Il sera très difficile de répondre à votre question sans avoir accès au fichier. Pouvez-vous déposer votre fichier, et votre question sur un de nos forums?
Voici le lien vers nos forums https://www.lecfomasque.com/forums
Merci.
Sophie
Entendu je le fait de ce pas, merci pour votre réponse
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
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.
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
Bonjour,
Une question svp,
Comment consolider avec power query des onglets identifiés par le nom de même structure, de plusieurs fichiers qui se trouvent dans un seul dossier, mais qui sont masqués (Xlsveryhidden) ?
Merci d’avance.
Bonjour Liams, voici le lien vers notre forum Power Query dans lequel vous pourrez poser votre question avec un fichier exemple. La communauté se fera un plaisir de vous proposer une solution. Bonne journée!