Excel : Fusionner des fichiers avec des noms d’onglets différents à l’aide de Power Query

L’article suivant a été rédigé suite à une question que j’ai reçue sur YouTube. L’individu demandait comment fusionner les données de plusieurs fichiers Excel quand le nom de l’onglet à fusionner n’est pas écrit de la même façon d’un fichier à l’autre. D’abord, sachez que j’ai déjà rédigé plusieurs articles sur les façons de fusionner des données provenant de différents fichiers Excel, sans répondre précisément à cette question. Voici les articles en question:

• Power Query: Importer, transformer et fusionner des tables de données dans Excel
• Power Query : Combiner plusieurs fichiers Excel
• Power BI : La nouvelle façon de combiner les fichiers Excel d’un dossier

Dans le présent article, je mettrai davantage le focus sur la fusion de fichiers avec des noms d’onglets différents. Pour réaliser cet exercice, vous devez utiliser Excel 2016 ou encore Excel 2010/2013 et ajouter le complément gratuit Power Query. Évidemment, le nom d’onglet peut différer mais la structure des données doit être la même. Par ailleurs, la meilleure pratique d’affaires serait de modifier le nom de vos onglets à la source, mais dans le cas où cela est impossible, je vous présente ici une alternative.

Fusionner des onglets de différents fichiers avec des noms d’onglets différents

Power Query est mis à jour régulièrement. Ce faisant, au moment où je rédige cet article, il est possible d’importer tous les fichiers d’un dossier et de les fusionner en choisissant l’option Combiner et Modifier à l’importation. Toutefois, pour utiliser cette option, tous les noms d’onglets des fichiers à fusionner doivent être identiques. Et à la question qui m’est souvent posée, pouvons-nous combiner le contenu de plusieurs onglets, la réponse est oui, mais encore une fois, pas directement via l’option Combiner et modifier. Il faut faire une petit détour, décrit ci-dessous.

Vous devez analyser de grandes quantités de données et les présenter dans des rapports et tableaux de bord, avec des indicateurs de performance pertinents ? Développez vos compétences avec nos formations en Power BI.

Dans un premier temps, assurez-vous que tous vos fichiers à fusionner soient enregistrés dans le même dossier et utilisez l’option Obtenir des données / À partir d’un fichier / À partir d’un dossier.

Si toutes vos données étaient comprises dans des onglets portant le même nom, vous pourriez tout simplement choisir l’option COMBINER ET MODIFIER du menu déroulant COMBINER. Mais quand les noms d’onglets à fusionner ne sont pas identiques, vous devez plutôt choisir l’option MODIFIER.

Ce faisant, vous obtiendrez une requête semblable à la suivante (ici, mes données sont dans deux fichiers portant chacun deux onglets):

Pour récupérer le contenu de vos fichiers, vous devrez d’abord ajouter une colonne personnalisée:

Dans cette colonne personnalisée, vous utiliserez le code M, de la façon suivante:

Vous pourrez ensuite supprimer toutes les colonnes de votre requête sauf cette nouvelle colonne personnalisée.

Vous pourrez ensuite cliquer sur le menu d’expansion de la colonne (les deux flèches dans le coin supérieur droit) et cocher les cases suivantes:

Ceci vous permettra d’avoir plus d’information sur le contenu de vos différents fichiers. Par exemple, ci-dessous, on voit que l’on a 4 onglets. Ces 4 onglets, souvenons-nous, proviennent de 2 fichiers différents. Il pourrait aussi y avoir des tables dans ces onglets. Ces informations sont importantes puisque comme nous ne pouvons pas fusionner tous les onglets sans distinction, nous devrons filter les données selon certaines règles et il est fort à parier que les données de ces colonnes nous serons utiles pour rédiger des règles.

Dans mon exemple, je cherche deux résultats, soit la fusion des onglets Ventes (du fichier FilialeA) et Feuil1 (du fichier FilialeB) et la fusion des onglets Coûts (du fichier FilialeA) et Feuil2 (du fichier FilialeB). Ce faisant, à ce stade-ci, je fais 2 références à ma requête en cours Sources.

Je nomme ces 2 requêtes Ventes et Coûts. Ces deux requêtes s’alimenteront à partir de la requête Sources, présentée dans les étapes ci-dessus.

Si je n’ai aucune façon d’établir une règle pour fusionner les fichiers, je devrai malheureusement faire un filtre manuellement ou mieux, à partir d’une liste ou d’un paramètre de liste, qui pourrait être alimenté par le biais d’un tableau Excel, par exemple, et que je pourrais maintenir à jour, dans ma solution. Cette liste contiendrait donc le nom de tous les onglets à fusionner.

Ici, si j’explose le tout, j’obtiens ce qui suit. Je peux ensuite utiliser la première ligne comme en-tête de colonnes et décocher le mot MOIS dans la colonne Mois et au besoin, effectuer un REGROUPER PAR.

Si je voulais également une colonne qui me permet d’identifier la filiale, je pourrais revenir dans ma requête Sources et m’assurer de ne pas effacer la colonne Name. Je pourrais par la suite extraire les informations de cette colonne pour me débarrasser des extensions .xlsx et ne conserver que les noms de filiales. Ces noms de filiales apparaîtraient alors dans mes requêtes Ventes et Coûts.

Autres pistes de solutions possibles

Supposons donc qu’il n’y ait aucune façon de fixer une règle pour reconnaître le fait que je doive fusionner l’onglet Ventes avec Feuil1 et l’onglet Coûts avec Feuil2, je pourrais utiliser d’autres ruses. Par exemple, ici, je pourrais ajouter une colonne Index et une colonne qui m’indique si l’index est pair ou non (Informations). Par la suite, je n’aurais qu’à filtrer sur les False pour fusionner mes ventes sur les True pour fusionner mes coûts. Habituellement, si on cherche, on trouve toujours une règle à insérer, selon le contexte de nos données.

La question sur YouTube mentionnait que les noms d’onglets étaient comme suit:

• 01-2018
• 02-2018
• et.

Ce cas serait donc très simple à traiter. S’il n’y a qu’un seul onglet par fichier, il n’y aurait pas de règles à insérer. On ne ferait que fusionner. Et si on voulait récupérer le numéro du mois et de l’année, il s’agirait de fractionner la colonne des noms d’onglets par “_” . S’il y a plus d’un onglet par fichier, alors encore une fois, il faudrait établir une règle selon le nom de l’onglet ou le type de données (table, onglet, etc.).

En espérant que ce court tutoriel ait pu répondre à votre question, cher YouTubeur.

Fichier d’accompagnement VIP à télécharger

Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.

Formation complémentaire

Afin d’approfondir vos connaissances avec Power BI et plus particulièrement vos compétences avec l’éditeur de requête Power Query, nous vous recommandons notre formation Recettes magiques pour transformer vos données.

Voici quelques commentaires d’apprenants ayant suivi la formation en ligne Recettes magiques pour transformer vos données :

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é.