Fusionner et Ajouter Tables dans PowerQuery

Vous avez sans doute remarqué que dans PowerQuery, il existe deux fonctionnalités particulières: Fusionner et Ajouter. Pour la partie Fusionner, vous trouverez un paramètre appelé Type de jointure. L’objectif de ce post est de bien comprendre l’impact de chacune des possibilités offertes par tel ou tel type de jointure. Nous allons donc les analyser une par une. Et pour ceux qui connaissent le langage SQL, je vous mettrai la correspondance en terme de requête.

Il existe 6 types de jointure dans la fusion des tables sur PowerQuery

  1. Externe gauche (LEFT OUTER JOIN)
  2. Externe droite (RIGHT OUTER JOIN)
  3. Externe entière (FULL OUTER JOIN)
  4. Interne (INNER JOIN)
  5. Gauche opposée (LEFT OUTER JOIN where P2.PRODUIT is Null)
  6. Droite opposée (RIGHT OUTER JOIN where P1.PRODUIT is Null)

Les données

Commencez tout d’abord par recopier les deux tableaux suivants en les nommant respectivement T.Produits1 et T.Produits2:

jointures_01_donnees

Création du tableau

Tapez d’abord les titres et les données comme dans l’exemple ci-dessus.

  • Sélectionnez une cellule du premier tableau
  • Accueil > Styles > Mettre sous forme de tableau > Choisissez un tableau > Cochez “mon tableau comporte des en-têtes”
  • Le tableau a été créé
  • Sélectionnez une cellule du tableau
  • Outils de tableau > Création > Propriétés > Nom du tableau: T.Produits1
  • Refaire la même chose avec le second tableau et nommez-le T.Produits2

Création des requêtes

Ce qui suit a été réalisé avec la version 2016 d’Excel mais vous pouvez également le faire avec Excel 2010 ou 2013 après avoir installé PowerQuery. Le ruban est légèrement différent.

  • Sélectionnez une cellule de votre premier tableau
  • 2010 ou 2013: PowerQuery > Données Excel > A partir d’un tableau
  • 2016: Données > Récupérer et transformer > A partir d’un tableau

Pour la suite, je ne préciserai pas les différences sauf si cela s’avère nécessaire.

A présent, vous devez vous trouver dans l’éditeur de requêtes PowerQuery.

  • Accueil > Fermer > Fermer et charger dans > Créer une connexion uniquement
  • Refaire les même manipulation avec le second tableau.

Vous devriez avoir l’écran suivant:

jointures_02_donnees

Fusionner les tableaux

Nous allons à présent pouvoir regarder ce qui se passe lorsque nous fusionnons deux tableaux avec des types de jointure différents.

Externe gauche

Je vais à nouveau faire la distinction entre les différentes versions d’Excel pour ce point.

  • 2010 ou 2013: Power Query > Combiner > Fusionner
  • 2016: Données > Récupérer et transformer > Nouvelle requête > Combiner des requêtes > Fusionner

jointures_03_fusionner

  • Renseignez les éléments comme dans la copie d’écran ci-dessus
    1. Choisissez la première requête qui correspond au premier tableau
    2. Sélectionnez la colonne Produit (ce sera la clé de la première requête)
    3. Choisissez la seconde requête qui correspond au second tableau
    4. Sélectionnez la colonne Produit (ce sera la clé de la second requête)
    5. Type de jointure: Externe gauche (Vous pouvez lire en bas de la fenêtre: La sélection a retourné 3 lignes sur les 5 initiales.)
    6. Tapez sur OK

Nous sommes en train de créer une nouvelle requête qui nous permet de fusionner les deux requêtes précédemment créées d’après leur clé. Avec ce type de jointure, pour chaque ligne de la requête de gauche (Table T.Produits1),  nous allons rechercher toutes les correspondances dans la seconde requête en se basant sur la colonne Produit pour chacune des requêtes.

  • Cliquez ensuite sur l’icône Développer à droite du titre de la colonne NewColumn
  • Décochez la case “Utiliser le nom de la colonne d’origine comme préfixe”
  • Cliquez sur OK
  • Accueil > Fermer > Fermer et charger dans
  • Choisissez Table puis Feuille de calcul existante: $H$5

La nouvelle table fusionnée vient d’être créée:

jointures_04_fusionner

Nous avons donc bien nos deux premières colonnes à gauche correspondant à la première table et la correspondance dans les deux colonnes suivantes. Pour les poires et framboises, aucune correspondance n’a été trouvée et la cellule est vide.

Cela ne vous rappelle t-il pas quelque chose ? … Une certaine fonction RECHERCHEV… Nous venons en quelques clics de faire une jointure comme un RECHERCHEV, sans la gestion des erreurs et en ramenant toutes les colonnes de la seconde table. Imaginez avec un nombre plus important de colonnes … !

Au niveau SQL, pour ceux qui connaissent, nous aurions la requête suivante:

SELECT * 
FROM T.Produits1 P1 LEFT OUTER JOIN T.Produits2 P2 ON P1.Produit = P2.Produit ;

Externe droite

Nous allons ensuite refaire les mêmes étapes en ne changeant que le type de jointure. Au lieu de Externe gauche, nous allons mettre Externe droite.

Les changements par rapport à l’exercice précédent sont en gras italique.

  • 2010 ou 2013: Power Query > Combiner > Fusionner
  • 2016: Données > Récupérer et transformer > Nouvelle requête > Combiner des requêtes > Fusionner
  • Dans la fenêtre:
    1. Choisissez la première requête qui correspond au premier tableau
    2. Sélectionnez la colonne Produit (ce sera la clé de la première requête)
    3. Choisissez la seconde requête qui correspond au second tableau
    4. Sélectionnez la colonne Produit (ce sera la clé de la second requête)
    5. Type de jointure: Externe gauche
    6. Tapez sur OK
  • Cliquez ensuite sur l’icône Développer à droite du titre de la colonne NewColumn
  • Décochez la case “Utiliser le nom de la colonne d’origine comme préfixe”
  • Cliquez sur OK
  • Accueil > Fermer > Fermer et charger dans
  • Choisissez Table puis Feuille de calcul existante: $H$14

La nouvelle table fusionnée vient d’être créée:

jointures_05_fusionner

Nous constatons donc à travers cette nouvelle table créée que nous avons bien toute notre seconde table et les correspondances à la première si celle-ci est trouvée (Pommes, Fraises et Abricots).

Au niveau SQL, nous aurions la requête suivante:

SELECT * 
FROM T.Produits1 P1 RIGHT OUTER JOIN T.Produits2 P2 ON P1.Produit = P2.Produit ;

Externe entière

Nous allons ensuite refaire les mêmes étapes en ne changeant que le type de jointure. Au lieu de Externe droite, nous allons mettre Externe entière. Comme vous pouvez l’imaginez, nous allons avoir un mixte des deux précédentes fusions.

Les changements par rapport à l’exercice précédent sont en gras italique.

  • 2010 ou 2013: Power Query > Combiner > Fusionner
  • 2016: Données > Récupérer et transformer > Nouvelle requête > Combiner des requêtes > Fusionner
  • Dans la fenêtre:
    1. Choisissez la première requête qui correspond au premier tableau
    2. Sélectionnez la colonne Produit (ce sera la clé de la première requête)
    3. Choisissez la seconde requête qui correspond au second tableau
    4. Sélectionnez la colonne Produit (ce sera la clé de la second requête)
    5. Type de jointure: Externe entière
    6. Tapez sur OK
  • Cliquez ensuite sur l’icône Développer à droite du titre de la colonne NewColumn
  • Décochez la case “Utiliser le nom de la colonne d’origine comme préfixe”
  • Cliquez sur OK
  • Accueil > Fermer > Fermer et charger dans
  • Choisissez Table puis Feuille de calcul existante: $H$24

La nouvelle table fusionnée vient d’être créée:

jointures_06_fusionner

Comme nous l’avions prévu, nous avons donc l’ensemble des correspondances entre les deux tables. Et même s’il n’y a pas correspondance, nous pouvons le voir aisément. Très pratique pour faire des analyses de données. Mais attention, si vous faites ce genre d’analyse, n’oubliez pas de nettoyer vos données au préalable (suppression des espaces, des caractères non imprimables, …).

Au niveau SQL, nous aurions la requête suivante:

SELECT * 
FROM T.Produits1 P1 FULL OUTER JOIN T.Produits2 P2 ON P1.Produit = P2.Produit ;

Interne

Nous en avons fini avec les jointures externes. A présent, je vous propose l’utilisation de la jointure interne, une des jointures les plus utilisées dans SQL car elle nous permet de ne voir que les correspondances exactes. Au lieu de Externe entière, nous allons mettre Interne

Les changements par rapport à l’exercice précédent sont en gras italique.

  • 2010 ou 2013: Power Query > Combiner > Fusionner
  • 2016: Données > Récupérer et transformer > Nouvelle requête > Combiner des requêtes > Fusionner
  • Dans la fenêtre:
    1. Choisissez la première requête qui correspond au premier tableau
    2. Sélectionnez la colonne Produit (ce sera la clé de la première requête)
    3. Choisissez la seconde requête qui correspond au second tableau
    4. Sélectionnez la colonne Produit (ce sera la clé de la second requête)
    5. Type de jointure: Interne 
    6. Tapez sur OK
  • Cliquez ensuite sur l’icône Développer à droite du titre de la colonne NewColumn
  • Décochez la case “Utiliser le nom de la colonne d’origine comme préfixe”
  • Cliquez sur OK
  • Accueil > Fermer > Fermer et charger dans
  • Choisissez Table puis Feuille de calcul existante: $M$5

La nouvelle table fusionnée vient d’être créée:

jointures_07_fusionner

La table est à présent beaucoup plus petite car seules les correspondances exactes entre les deux tables ont été remontées dans la rquête fusionnée.

Au niveau SQL, nous aurions la requête suivante:

SELECT * 
FROM T.Produits1 P1 INNER JOIN T.Produits2 P2 ON P1.Produit = P2.Produit ;

Gauche opposée

Celui-ci peut sembler un peu moins évident en terme de désignation. Mais vous allez voir qu’il peut être très pratique puisqu’il nous permet de remonter uniquement les valeurs de la table1 qui n’ont pas de correspondance dans la table2. Au lieu de Internenous allons mettre Gauche opposée

Les changements par rapport à l’exercice précédent sont en gras italique.

  • 2010 ou 2013: Power Query > Combiner > Fusionner
  • 2016: Données > Récupérer et transformer > Nouvelle requête > Combiner des requêtes > Fusionner
  • Dans la fenêtre:
    1. Choisissez la première requête qui correspond au premier tableau
    2. Sélectionnez la colonne Produit (ce sera la clé de la première requête)
    3. Choisissez la seconde requête qui correspond au second tableau
    4. Sélectionnez la colonne Produit (ce sera la clé de la second requête)
    5. Type de jointure: Gauche opposée 
    6. Tapez sur OK
  • Cliquez ensuite sur l’icône Développer à droite du titre de la colonne NewColumn
  • Décochez la case “Utiliser le nom de la colonne d’origine comme préfixe”
  • Cliquez sur OK
  • Accueil > Fermer > Fermer et charger dans
  • Choisissez Table puis Feuille de calcul existante: $M$12

La nouvelle table fusionnée vient d’être créée:

jointures_08_fusionner

Il s’agit finalement du complément de la table fusionnée Interne mais uniquement pour la table Produits1. Vous comprendrez donc aisément qu’avec le type de jointure Droite opposée, nous aurons le complément de la table Produits2.

Si nous allons plus loin, nous pouvons même dire que:

Externe entière = Interne + Gauche opposée + Droite opposée.

Au niveau SQL, nous aurions la requête suivante:

SELECT * 
FROM T.Produits1 P1 LEFT OUTER JOIN T.Produits2 P2 ON P1.Produit = P2.Produit 
WHERE P2.Produit is Null;

Droite opposée

Du coup, nous allons avoir exactement l’inverse de la Gauche opposée, à savoir les valeurs de la table2 qui n’ont pas de correspondance dans la table1. Au lieu de Gauche opposéenous allons mettre Droite opposée

Les changements par rapport à l’exercice précédent sont en gras italique.

  • 2010 ou 2013: Power Query > Combiner > Fusionner
  • 2016: Données > Récupérer et transformer > Nouvelle requête > Combiner des requêtes > Fusionner
  • Dans la fenêtre:
    1. Choisissez la première requête qui correspond au premier tableau
    2. Sélectionnez la colonne Produit (ce sera la clé de la première requête)
    3. Choisissez la seconde requête qui correspond au second tableau
    4. Sélectionnez la colonne Produit (ce sera la clé de la second requête)
    5. Type de jointure: Droite opposée 
    6. Tapez sur OK
  • Cliquez ensuite sur l’icône Développer à droite du titre de la colonne NewColumn
  • Décochez la case “Utiliser le nom de la colonne d’origine comme préfixe”
  • Cliquez sur OK
  • Accueil > Fermer > Fermer et charger dans
  • Choisissez Table puis Feuille de calcul existante: $M$18

La nouvelle table fusionnée vient d’être créée:

jointures_09_fusionner

Au niveau SQL, nous aurions la requête suivante:

SELECT * 
FROM T.Produits1 P1 RIGHT OUTER JOIN T.Produits2 P2 ON P1.Produit = P2.Produit 
WHERE P1.Produit is Null;

Ajouter des tableaux

Nous venons de voir toutes les fusions possibles dans PowerQuery. Mais lorsque vous avez choisi “Combiner les requêtes“, vous avez dû remarquer qu’il était possible de les ajouter. D’ailleurs, ce serait bien d’avoir l’ensemble des données dans une seule table avec idéalement la somme des quantités si nous avons le même fruit.

Commençons d’abord par ajouter les tables.

Ajouter

Pour cela, rien de plus simple puisqu’il s’agit de la même manipulation ou presque que vous venez de faire à 6 reprises.

  • 2010 ou 2013: Power Query > Combiner > Ajouter
  • 2016: Données > Récupérer et transformer > Nouvelle requête > Combiner des requêtes > Ajouter

Une nouvelle fenêtre apparaît. Vous pouvez choisir si vous allez ajouter 2 ou plus de tables. Dans notre cas, nous allons sélectionner deux tables.

  • Table primaire : T.Produits1
  • Table à ajouter à la table primaire : T.Produits2
  • Accueil > Fermer > Fermer et charger dans
  • Choisissez Table puis $R$5

Vous pouvez constater que les deux tables ont simplement été ajoutées l’une à la suite de l’autre. Il est très important lorsqu’on ajoute des table de s’assurer que les titres des colonnes sont rigoureusement identiques car PowerQuery s’appuie sur les titres pour les Ajouts. Si j’avais par exemple la table 2 avec la colonne Produits (avec un “s”), PowerQuery aurait ajouter une nouvelle colonne Produits pour laquelle les valeurs seraient nulles pour les lignes correspondant à la Table1.

jointures_10_ajouter

Regroupement

Nous avons donc bien ajouté nos deux tables mais l’idéal serait de regrouper les produits identiques en réalisant la somme des quantités. Dans PowerQuery, cela est très simple à réaliser.

  • Données > Récupérer et transformer > Afficher les requêtes (il faut que le panneau de droite des requêtes soit affiché)
  • Sélectionnez la dernière requête (Append1) puis Clic-droit > Modifier
  • Sélectionnez la colonne Produit
  • Accueil > Transformer > Regrouper par
  • Choisissez les options comme dans l’écran ci-dessous:

jointures_11_regrouper

  • Cliquez sur OK
  • Accueil > Fermer et charger

Vous pouvez à présent voir votre nouvelle table regroupant les quantités comme souhaité:

jointures_12_regrouper

Vous pouvez constater que pour les pommes, nous avons bien les quantités qui ont été additionnées: 5 + 12 = 17.

Conclusion

Comme nous l’avons vu, les fusions et les ajouts de tables peuvent être très pratiques, soit pour enrichir des données, réaliser une analyse de tables, appliquer des Recherchev en deux clics. Mais il faut bien comprendre les différences entre tous les types de jointures. Voici donc une copie d’écran qui résume l’ensemble:

jointures_synthese

Laisser un commentaire

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