01 octobre 2018

|

par : jeremy

|

Catégories: Excel, PowerQuery

Comment comparer le contenu de 2 listes et extraire les éléments de la première liste qui ne sont pas dans la seconde avec PowerQuery ?

Vous avez deux listes assez longues et vous souhaitez connaître les éléments de la première liste qui ne sont pas dans la seconde. Pour cela, nous allons utiliser Power Query et vous allez voir que finalement, c'est assez simple.

Pour cet exercise j’ai choisi d’utiliser 2 listes de prénom :

Liste1_Liste2_01

Etape 1: Enregistrer la liste 1

La première étape consiste à enregistrer les deux listes. Nous allons d’abord procéder avec la liste 1, puis il faudra reproduire les mêmes étapes avec la seconde liste.

Sélectionnez tout d'abord un élément de la liste1

Ensuite dans le ruban : Données > Récupérer et transformer des données > À partir d'un tableau ou d'une plage.

La sélection du tableau en entier sera faite automatiquement.

Si la première ligne n'est pas un en-tête, décochez la case.

Liste1_Liste2_03

L’éditeur Power Query va alors apparaître. La seule action à réaliser est de fermer et charger les données. Mais attention, cliquez bien dans le bas du bouton pour sélectionner « Fermer et charger dans… ».

L’éditeur Power Query va alors se fermer et une nouvelle fenêtre va alors apparaître sur la feuille Excel. Choisissez « Ne créer que la connexion », veillez à ce que la case à cocher en bas est bien désactivée puis cliquez sur OK.

Vous verrez alors sur la droite de votre fichier Excel un nouveau volet apparaître avec une première requête appelée « Tableau1 ».

Vous venez d’enregistrer votre première liste.

Etape 2: Enregistrer la seconde liste

A présent, veuillez refaire l’étape 1 pour la seconde liste. Vous devriez arriver au résultat suivant :

Liste1_Liste2_07

Etape 3: Créer le lien entre les tables

Nous avons donc nos deux listes bien enregistrées. L’objectif à présent est de les relier. Ainsi nous pourrons mettre en avant les lignes qui ne se trouvent pas dans la liste 2. Nous pourrions également remonter toutes les différences entre les deux listes.

Pour cela, allez dans le ruban : Données > Récupérer et transformer des données > Obtenir des données > Combiner des requêtes > Fusionner.

Une fenêtre apparaît alors pour créer notre lien. Choisissez d’abord le tableau1 puis sélectionnez la colonne. Refaite la même chose au-dessous avec le tableau2. Enfin, choisissez le premier type de jointure, comme ci-dessous :

Puis cliquez sur OK. Vous allez à nouveau ouvrir automatiquement l’éditeur Power Query. Vous remarquerez que nous avons à présent deux colonnes : Liste 1, qui correspond à la première liste et Tableau2 qui contient des cellules nommées Table. L’objectif est à présent de développer cette table. Cliquez sur le petit icône à droite du titre de la colonne.

Dans la nouvelle fenêtre, vous pouvez voir la colonne du tableau2, à savoir Liste2. Cliquez simplement sur OK.

Vous voyez ainsi la correspondance entre les deux listes. Remarquez que lorsque un élément est dans la liste 1 et n’est pas dans la seconde liste, la valeur est null en italique.

Etape 4: conserver les lignes de la liste 1 et non présentes dans la liste 2

Vous comprenez donc qu’il va falloir conserver uniquement les lignes lorsque la valeur de la Liste2 est null. Cliquez sur l’icône filtre, désélectionnez tout puis sélectionnez (null). Terminez en cliquant sur OK.

Il suffit ensuite de supprimer la colonne Liste2 (clic-droit puis supprimer colonne) pour avoir notre liste attendue :

Etape 5: Charger les données

Pour charger les données, faites comme vous l’avez fait déjà à deux reprises :

Ruban > Fermer et charger > Fermer et charger dans...

L’éditeur Power Query disparaît et une fenêtre apparaît sur Excel. Contrairement aux deux fois précédentes, vous allez choisir les éléments suivants:

  • Tableau
  • Feuille de calcul existantes et vous sélectionnez la cellule E1
  • Décochez « Ajouter ces données au modèle de données »
  • Cliquez sur OK.

Conclusion

Vous devez obtenir ce résultat :

Vous pouvez voir la nouvelle liste qui contient uniquement les éléments de la liste 1 qui ne se trouvent pas dans la liste 2. Si vous rajoutez des éléments dans une des deux listes (ou les deux) cliquez sur :

ruban > Données > Actualiser tout

Votre nouvelle liste se mettra à jour automatiquement. En effet, le travail que vous avez réalisé est enregistré. Et vous n’avez pas programmé une seule ligne !