Avec Power Query, travailler avec des données dispersées dans des feuilles de calcul ou même des classeurs est devenu plus facile.
L'une des choses pour lesquelles Power Query peut vous faire gagner beaucoup de temps est lorsque vous devez fusionner des tables de tailles et de colonnes différentes en fonction d'une colonne correspondante.
Vous trouverez ci-dessous une vidéo dans laquelle je montre exactement comment fusionner des tableaux dans Excel à l'aide de Power Query.
Si vous préférez lire le texte plutôt que regarder une vidéo, vous trouverez ci-dessous les instructions écrites.
Supposons que vous ayez un tableau comme indiqué ci-dessous :
Ce tableau contient les données que je souhaite utiliser, mais il manque encore deux colonnes importantes : l'« Identifiant du produit » et la « Région » où le représentant commercial opère.
Ces informations sont fournies sous forme de tableaux séparés, comme indiqué ci-dessous :
Pour obtenir toutes ces informations dans un seul tableau, vous devrez fusionner ces trois tableaux afin de pouvoir ensuite créer un tableau croisé dynamique et l'analyser, ou l'utiliser à d'autres fins de reporting/dashboard.
Et par fusion, je ne veux pas dire un simple copier-coller.
Vous devrez mapper les enregistrements pertinents du tableau 1 avec les données des tableaux 2 et 3.
Maintenant, vous pouvez compter sur RECHERCHEV ou INDEX/MATCH pour le faire.
Ou si vous êtes un as de VBA, vous pouvez écrire un code pour le faire.
Mais ces options prennent du temps et sont compliquées par rapport à Power Query.
Dans ce tutoriel, je vais vous montrer comment fusionner ces trois tableaux Excel en un seul.
Pour que cette technique fonctionne, vous devez avoir des colonnes de connexion. Par exemple, dans les tableaux 1 et 2, la colonne commune est « Article », et dans les tableaux 1 et 3, la colonne commune est « Représentant commercial ». Notez également qu'il ne doit pas y avoir de répétition dans ces colonnes de connexion.Remarque : Power Query peut être utilisé comme complément dans Excel 2010 et 2013 et est une fonctionnalité intégrée à partir d'Excel 2016. En fonction de votre version, certaines images peuvent sembler différentes (les captures d'images utilisées dans ce didacticiel proviennent d'Excel 2016).
Fusionner des tables à l'aide de Power Query
J'ai nommé ces tables comme indiqué ci-dessous :
- Tableau 1 - Sales_Data
- Tableau 2 - Pdt_Id
- Tableau 3 - Région
Il n'est pas obligatoire de renommer ces tables, mais il est préférable de donner des noms qui décrivent de quoi parle la table.
En une seule fois, vous ne pouvez fusionner que deux tables dans Power Query.
Nous devrons donc d'abord fusionner les tableaux 1 et 2, puis fusionner le tableau 3 à l'étape suivante.
Fusion du tableau 1 et du tableau 2
Pour fusionner des tables, vous devez d'abord convertir ces tables en connexions dans Power Query. Une fois que vous avez les connexions, vous pouvez facilement les fusionner.
Voici les étapes pour enregistrer un tableau Excel en tant que connexion dans Power Query :
- Sélectionnez n'importe quelle cellule dans la table Sales_Data.
- Cliquez sur l'onglet Données.
- Dans le groupe Get & Transform, cliquez sur « From Table/Range ». Cela ouvrira l'éditeur de requête.
- Dans l'éditeur de requête, cliquez sur l'onglet « Fichier ».
- Cliquez sur l'option « Fermer et charger vers ».
- Dans la boîte de dialogue « Importer des données », sélectionnez « Créer uniquement une connexion ».
- Cliquez sur OK.
Les étapes ci-dessus créeraient une connexion avec le nom Sales_Data (ou tout autre nom que vous avez donné au tableau Excel).
Répétez les étapes ci-dessus pour les tableaux 2 et 3.
Ainsi, lorsque vous aurez terminé, vous aurez trois connexions (avec le nom Sales_Data, Pdt_Id et Region).
Voyons maintenant comment fusionner les tables Sales_Data et Pdt_Id.
- Cliquez sur l'onglet Données.
- Dans le groupe Obtenir et transformer des données, cliquez sur Obtenir des données.
- Dans la liste déroulante, cliquez sur Combiner les requêtes.
- Cliquez sur Fusionner. Cela ouvrira la boîte de dialogue Fusionner.
- Dans la boîte de dialogue Fusionner, sélectionnez « Sales_Data » dans la première liste déroulante.
- Sélectionnez « Pdt_Id » dans le deuxième menu déroulant.
- Dans l'aperçu 'Sales_Data', cliquez sur la colonne 'Item'. Cela sélectionnera la colonne entière.
- Dans l'aperçu « Pdt_Id », cliquez sur la colonne « Article ». Cela sélectionnera la colonne entière.
- Dans la liste déroulante « Rejoindre le type », sélectionnez « Externe gauche (tout à partir du premier, correspondant à partir du deuxième) ».
- Cliquez sur OK.
Les étapes ci-dessus ouvriraient l'éditeur de requête et vous montreraient les données de Sales_Data avec une colonne supplémentaire (de Pdt_Id).
Fusion des tableaux Excel (tableaux 1 et 2)
Maintenant, le processus de fusion des tables se déroulera dans l'éditeur de requête avec les étapes suivantes :
- Dans la colonne supplémentaire (Pdt_Id), cliquez sur la flèche à double pointe dans l'en-tête.
- Dans la boîte d'options qui s'ouvre, décochez tous les noms de colonnes et sélectionnez uniquement Article. En effet, nous avons déjà la colonne de nom de produit dans la table existante et nous voulons uniquement l'ID de produit pour chaque produit.
- Décochez l'option "Utiliser le nom de colonne d'origine comme préfixe".
- Cliquez sur OK.
Cela vous donnerait la table résultante qui contient chaque enregistrement de la table Sales_Data et une colonne supplémentaire qui contient également les identifiants de produit (de la table Pdt_Id).
Maintenant, si vous ne souhaitez combiner que deux tableaux, vous pouvez charger cet Excel, vous avez terminé.
Mais nous avons trois tables à fusionner, il reste donc du travail à faire.
Vous devez enregistrer cette table résultante en tant que connexion (afin que nous puissions l'utiliser pour la fusionner avec la table 3).
Voici les étapes pour enregistrer cette table fusionnée (avec les données de la table Sales_Data et Pdt_Id) en tant que connexion :
- Cliquez sur l'onglet Fichier
- Cliquez sur l'option « Fermer et charger sur ».
- Dans la boîte de dialogue « Importer des données », sélectionnez « Créer uniquement une connexion ».
- Cliquez sur OK.
Cela enregistrera les données nouvellement fusionnées en tant que connexion. Vous pouvez renommer cette connexion si vous le souhaitez.
Fusion du tableau 3 avec le tableau résultant
Le processus de fusion de la troisième table avec la table résultante (que nous avons obtenue en fusionnant la Table 1 et la Table 2) est exactement le même.
Voici les étapes pour fusionner ces tables :
- Cliquez sur l'onglet Données.
- Dans le groupe Obtenir et transformer des données, cliquez sur « Obtenir des données ».
- Dans la liste déroulante, cliquez sur « Combiner les requêtes ».
- Cliquez sur "Fusionner". Cela ouvrira la boîte de dialogue Fusionner.
- Dans la boîte de dialogue Fusionner, sélectionnez « Fusionner1 » dans la première liste déroulante.
- Sélectionnez « Région » dans le deuxième menu déroulant.
- Dans l'aperçu « Merge1 », cliquez sur la colonne « Représentant commercial ». Cela sélectionnera la colonne entière.
- Dans l'aperçu de la région, cliquez sur la colonne « Représentant commercial ». Cela sélectionnera la colonne entière.
- Dans la liste déroulante « Rejoindre le genre », sélectionnez Externe gauche (tout à partir du premier, correspondant à partir du second).
- Cliquez sur OK.
Les étapes ci-dessus ouvriraient l'éditeur de requête et vous montreraient les données de Merge1 avec une colonne supplémentaire (Région).
Maintenant, le processus de fusion des tables se déroulera dans l'éditeur de requête avec les étapes suivantes :
- Dans la colonne supplémentaire (Région), cliquez sur la flèche à double pointe dans l'en-tête.
- Dans la boîte d'options qui s'ouvre, décochez tous les noms de colonnes et sélectionnez uniquement Région.
- Décochez l'option "Utiliser le nom de colonne d'origine comme préfixe".
- Cliquez sur OK.
Les étapes ci-dessus vous donneraient une table qui regroupe les trois tables (table Sales_Data avec une colonne pour Pdt_Id et une pour Region).
Voici les étapes pour charger ce tableau dans Excel :
- Cliquez sur l'onglet Fichier.
- Cliquez sur « Fermer et charger sur ».
- Dans la boîte de dialogue « Importer des données », sélectionnez les options Tableau et Nouvelles feuilles de calcul.
- Cliquez sur OK.
Cela vous donnerait le tableau fusionné résultant dans une nouvelle feuille de calcul.
L'une des meilleures choses à propos de Power Query est que vous pouvez facilement adapter les modifications aux données sous-jacentes (tableaux 1, 2 et 3) en les actualisant simplement.
Par exemple, supposons que Laura soit transférée en Asie et que vous obteniez de nouvelles données pour le mois suivant. Maintenant, vous n'avez plus besoin de répéter les étapes ci-dessus. Tout ce que vous avez à faire est de rafraîchir la table et tout recommencera pour vous.
En quelques secondes, vous aurez la nouvelle table fusionnée.
Vous aimerez peut-être également les didacticiels Power Query suivants :
- Combinez les données de plusieurs classeurs dans Excel (à l'aide de Power Query).
- Combinez les données de plusieurs feuilles de calcul dans une seule feuille de calcul dans Excel.
- Comment annuler le pivotement des données dans Excel à l'aide de Power Query (aka Get & Transform)
- Obtenir une liste des noms de fichiers à partir des dossiers et sous-dossiers (à l'aide de Power Query)
