4 façons de reproduire la fonction RECHERCHEV d’Excel dans Power Query

Publié le 03 novembre 2021
par Sophie Marchand M.Sc.
Fonction M Power Query

Le premier réflexe d’un débutant en Power Query, c’est de chercher à reproduire ce qu’il sait déjà faire avec Excel, mais en l’automatisant par le biais d’une requête. Bien qu’à terme, nous croyons qu’il faudra que ces usagers développent une façon d’aborder les données qui soit différente de celle dans Excel, nous comprenons ce genre de comportement. C’est pourquoi nous cherchons souvent sur ce blogue, à fournir des équivalences de fonction entre Excel et Power Query. Ceci permet d’apprivoiser tranquillement la solution, étape par étape. Aujourd’hui, nous nous penchons sur la fonction RECHERCHEV dans Power Query, avec des paramètres de recherche exacte et de recherche approximative.

 

Reproduire la fonction RECHERCHEV avec correspondance exacte dans Power Query

 

Avec les fonctionnalités de fusion de tables

 

Situation 1

Nous chercherons ici à retourner les quantités par item.

Table de coûts

 

Ces quantités se trouvent dans une table séparée, d’où le besoin de reproduire un RECHERCHEV dans Power Query.

Table dans Power Query

 

L’option la plus simple, dans ce cas-ci, serait d’utiliser la fonctionnalité qui permet de fusionner des tables côte-à-côte, en sélectionnant la colonne Item comme colonne commune.

Fusion dans Power Query

 

Une fois les champs de la deuxième table fusionnés à la première table, nous pouvons développer le champ Quantité (le champ Item fait déjà partie des données de la première table).

Power Query

 

Nous obtenons ainsi les quantités par item.

Power Query

 

Situation 2

Il arrive parfois que la fusion doive s’effectuer à partir de plus d’une colonne.

Power Query

 

Par exemple, ici, nous voulons fusionner la table précédente avec la table suivante pour les colonnes Produits et Groupe et non seulement à partir de la colonne Produits ou Groupe uniquement, pour éviter d’aboutir avec une duplication des informations..

Power Query

 

Pour se faire, il faut simpement sélectionner les deux colonnes de fusion dans chaque table.

Power Query

 

Ceci nous permet d’obtenir la table suivante, résultat équivalent à une fonction RECHERCHEV qui ferait une recherche sur les colonnes fusionnées, dans Power Query.

Power Query

 

Vous avez des sources de données volumineuses à rassembler et analyser dans Excel ? Apprenez à automatiser tout le processus d’importation, de transformation et de modélisation  des données avec nos formations sur Power Query et Power Pivot dans Excel.

 

Avec l’utilisation de listes

Il est intéressant de noter qu’il est également possible de reproduire une fonction RECHERCHEV dans Power Query à l’aide de fonctions de liste, afin d’obtenir encore plus de performance. Pour illustrer le tout, nous allons réutiliser les 2 premières tables de cet article, soit notre table d’items avec coûts et notre table d’items avec quantités. Dans notre table d’items avec coûts, nous allons insérer une fonction List.PositionOf afin d’obtenir la position des items dans notre table avec quantités, i.e. le numéro de ligne de chaque item.

Fonction M Power Query

 

Pour bien illustrer le concept, nous avons trié les quantités en ordre ascendant, dans notre table de quantités.

Colonne triée

 

Le résultat est le suivant. Ne pas oublier que dans Power Query, 0 est réellement 1, 1 est réellement 2, etc. Donc, ici, par exemple, on comprend que les pommes sont à la première ligne de la table de quantités et que les oranges, sont à la sixième ligne.

Power Query

 

Une fois qu’on sait sur quelle ligne aller récupérer l’information des quantités, dans la table quantité, on peut simplement ajouter la colonne personnalisée suivante, qui va lire dans la table de quantités, dans la colonne Quantité et sur la ligne déterminée par notre fonction précédente.

Power Query

 

Nous aboutissons ainsi avec le même résultat que dans notre premier exemple mais pour de très grosses tables, cette approche est plus optimale en termes de performance. On pourrait même améliorer davantage l’élégance et la performance de cette requête en imbriquant notre deuxième fonction dans notre première fonction et éviter ainsi une étape de transformation.

Résultat de fusion avec listes

 

Reproduire la fonction RECHERCHEV avec correspondance approximative dans Power Query

Pour reproduire une fonction RECHERCHEV avec des correspondances approximatives dans Power Query, on peut à nouveau utiliser les options de fusion de table. Par exemple, supposons que nous cherchions à obtenir les catégories (de la deuxième table ci-dessous) équivalentes aux quantités (de la première table ci-dessous).

Table dans Power Query

 

Autrement dit, toute valeur entre 100 et 200 devrait tomber dans “Très peu en demande”, entre 200 et 300, dans la catégorie “Peu en demande”, etc.

Power Query

 

Il s’agirait alors de fusionner nos tables mais cette fois-ci, en utilisant la colonne numérique de chaque table (Quantité et Valeur) comme colonne de fusion.

Power Query

 

Voici le résultat d’une telle fusion.

Table développée dans Power Query

 

Nous pourrions ensuite ajouter la colonne conditionnelle suivante.

Power Query

 

Et puis trier en ordre croissant les valeurs de cette nouvelle colonne.

Power Query

 

Il s’agirait ensuite de remplir les informations vers le bas de la colonne Catégorie.

Power Query

 

Pour obtenir le résultat intermédiaire suivant :

Power Query

 

Puis supprimer les colonnes non essentielles…

Power Query

 

Et les lignes avec des valeurs null dans la colonne Item.

Power Query

 


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 Query, nous vous recommandons de débuter avec notre formation Excel – Introduction à Power Query et au langage M.

 

Voici quelques commentaires d’apprenants ayant suivi cette formation :

Voici quelques commentaires d’apprenants ayant suivi la formation - Excel Introduction à Power Query et au langage M

CFO-Masque_Formations-en-ligne_FBLa 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é.

3 réflexions sur “4 façons de reproduire la fonction RECHERCHEV d’Excel dans Power Query”

  1. Bonjour,
    Merci pour cet article très intéressant !
    3ème cas : Avec l’utilisation de listes
    « On pourrait même améliorer davantage l’élégance et la performance de cette requête en imbriquant notre deuxième fonction dans notre première fonction.»
    Je me suis amusé à imbriquer les 2 fonctions et cela fonctionne très bien.
    =ItemsQtes[Quantité]{List.PositionOf(ItemsQtes[Item],[Item])}

    Dans le même ordre d’idée, est-il possible de ramener plusieurs colonnes à l’aide de fonction(s), plutôt que via le menu «Fusionner des requêtes»?
    Admettons par exemple, que la table ItemsQtes contienne une colonne avec le pays de production, et je veuille ramener la quantité et le pays de production dans la table ItemsCouts.

    Encore merci pour toutes ces découvertes !
    Guy

      1. Bonjour Audrée,
        Je viens de poser la question sur le forum Power Query.
        « Comment ramener plusieurs colonnes entre 2 tables à l’aide de fonction(s)? »
        Merci !
        Guy

Laisser un commentaire

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

Retour en haut